Questions tagged [google-sheets-cell-format]
Use for questions about Google Sheets cell format like formatting numbers, dates or using the equal, plus, minus symbols as part of texts (strings)
39
questions
64
votes
6answers
225k views
Formatting a cell with word wrap without switching modes?
How can one format a cell to have word wrap in Google Sheets?
I've tried Shift+Enter, but that doesn't seem to work.
One thing I know works is to place the spreadsheet into List Mode and then if you ...
36
votes
5answers
155k views
Leading zeros in Google Spreadsheet automatically removed
I have a column in which all inputs are numbers. But these numbers are actually not real numbers. Not real in the sense, 050 in this case is very different from 50
The problem is that whenever I add ...
26
votes
11answers
123k views
How to alter text orientation to vertical in Google Sheets?
Using MS Excel I can go to Format of the cell and choose the text orientation from horizontal to vertical fairly easily.
How do I alter text orientation to vertical in Google Sheets?
6
votes
1answer
8k views
How to convert all numbers regardless of decimal separator into valid number of current locale?
I have a column with text looking like this:
123,123
123.123
123,123
123.123
I would like another column to parse this into decimal numbers, disregarding the different decimal separators (dot . and ...
6
votes
4answers
10k views
How to change the text line height in one cell?
I could not find an option in the recent version of Google Sheets to change the text line height (the spacing between lines in a single cell).
You realize the problem even more when exporting the ...
5
votes
2answers
16k views
A Way to Add Text Before Your Hyperlink in Google Spreadsheets? [duplicate]
Is there a way to add text before the hyperlink?
So for example:
This section needs reworking. You can find the section HERE.
HERE would be the text replacement for the actual link. The actual ...
3
votes
3answers
64 views
Entering fractions verbatim in Google Sheets
I'm trying to put some marks in a spreadsheet and have a lot of unsimplified fractions or fractions like 19.5/20. Is there a way to enter fractions and have the cells be usable in other formulas, ...
3
votes
1answer
476 views
IMPORTHTML data is sporadically changing to incorrect format
I'm hoping you can help me with my problem using importhtml with Google Sheets. It is a problem similar to Trying to use Google Sheets importHTML() to import a table. It forces content to a date ...
2
votes
1answer
1k views
google cell format: only 3 digits
Is there any way to do this:
I have cell and most of the numbers have 2 significant digits like 3.45. But once in a while I have a number like 143 big number, the rest are under 10. Currently for 143 ...
2
votes
3answers
39 views
Google Sheets: avoid decimal point when integer - [Bug discovered?]
Is there a way to use a custom number format in Google Sheets so that decimal point will be visible only when decimals are present?
For instance
9
8,2
10
Is there a newer solution about achieving ...
2
votes
2answers
2k views
Automatic currency conversion in Google Sheets
I have a personal accounting sheet that is mainly accounting in Euros, but for every manual Euro (EUR) entry I do, I want to have that entry converted to USD in the previous column next to it ...
1
vote
2answers
41 views
How do I add dates for the beginning and end of the week in one cell
I know very little about Google Sheets and all I want to do is create a cell that will display the beginning and end of the week, like this: 7/22-7/28.
I'd like to be able to drag this across ...
1
vote
1answer
29 views
How do I keep borders when dragging down to fill cells?
I have a large scheduling spreadsheet of dates and names, split up by week, with borders for clarity. I want to be able to autofill the dates by dragging down the cell. Sometimes I also want to fill ...
1
vote
1answer
42 views
Unsimplified Fractions in Google Sheets
How can I get Google Sheets to display fractions in the form I enter them, rather than in a simplified form? I'm trying to evaluate how a student did on a test, so I have one cell that reports the ...
1
vote
1answer
24 views
Formating a number with specific spacing
I'm attempting to pull a particular option value from Yahoo! into Google Sheets. The base URL is: https://finance.yahoo.com/quote/GOOG190418C00800000
When breaking the formula up, I have to ...
1
vote
1answer
47 views
Google Spreadsheet: Conditional format used together with a range doesn't seem to format cells correctly
I'm trying to create a small calendar, which is built like this:
A1: = DATE(2018, 1, 1);
B4: = A1
C4: = A1 + 1
D4: = A1 + 2
…
This works fine and the columns B2 … D4 looks ok:
Mon, 01/01 | Tue, 02/...
1
vote
1answer
749 views
Convert multiple currencies and total the results in one cell
I have 4 currency columns (GBP, Euro, DKK, SKK). I then want to convert them to GBP in the 'Total Column. Is there a formula to look at all 4 currency columns and then convert to GBP in the specific ...
1
vote
0answers
44 views
How do you “Paint Format” more than once in Google Sheets?
In Google Sheets you can select a formatted row, click the "Paint Format" Tool, then click another row to apply the same format. You can drag down to do multiple rows if they are together (say rows 50-...
1
vote
0answers
653 views
Google Sheets text wrap no longer expands cell
All of a sudden I seem to be having trouble in my Google Sheets auto extending height of cell with text wrap, for certain cells.
Mac OS: 10.13.6 (17G65)
Chrome: Version 73.0.3683.103 (Official Build)...
1
vote
2answers
161 views
Date fields that refuse to be parsed/formatted
I have some very mysterious date fields in a huge (600 000 records) CSV file which absolutely refuses to behave.
They come up aligned left, and conditional formatting shows that they cannot be seen ...
1
vote
1answer
848 views
How to calculate time worked in Google Sheets after 12am?
I usually work from 3pm to 1am. I can't seem to find out how to make google sheets find the correct duration between those times. I know the correct duration is 10 hours, but no matter what I do ...
0
votes
3answers
197 views
Duration to seconds calculation
There are 3 columns in my google sheet with heading Start time (col C), End Time (col D) and time taken (col E). Column E is arrived by finding the difference between col D and col C. Now when I try ...
0
votes
1answer
13 views
Rich text formatting in a Google Sheets cell
How is it possible to enable "rich text formatting" inside a single Google Sheets cell?
I'd like to have a cell containing some text with both bold and non-bold content, words in italic, with some ...
0
votes
1answer
105 views
Partial cell formatting with CONCATENATE in Google Sheets
Is there a way to format partially using CONCATENATE in Google Sheets?
For example, =CONCATENATE("*", Sheet1!A1, Sheet1!B1), and I want to make the * bolded or blue in color or something else.
I ...
0
votes
2answers
900 views
How do I Specify the Scientific Notation Power in Google Sheets?
In Google Sheets when I enter
10E-12
in a cell formatted for scientific notation, it automatically turns the number into
1E-11
Though technically correct, this is really annoying when comparing ...
0
votes
1answer
112 views
Google Sheets Custom Number Format - Unchanging #<1000
I am well aware of the excellent Custom Number Formula:
[<999950]0.0,"K";[<999950000]0.0,,"M";0.0,,,"B"
Which abbreviates #,000 numbers to #K, #,000,000 to #M etc...
However, I am looking for ...
0
votes
2answers
17 views
When I edit a table in Google Sheet, it auto-changes my data
When I put '4.8.8' into a cell in Google Sheets, it automatically converts it to '4.8.2008'. How do I avoid it?
0
votes
1answer
194 views
How to format Brazilian phone numbers in Google Sheets?
I have two possible phone numbers:
One with 9 digits (mobile) and 8 digits (landline), and I need to insert - after 4 or 5 digits so they appear as 0000-0000 when 8 digits, and 00000-0000 when they ...
0
votes
1answer
5k views
How to remove insignificant decimal point in Google Spreadsheets?
I'm trying to format a number in Google Spreadsheets. I want it to show the thousands comma, then a period ., followed by mostly 3 digits, only when necessary.
For example:
1,234.56789 => 1,234....
0
votes
1answer
37 views
How to enumerate dates into a Google Sheets' column?
I need to enumerate a Google Sheets' column with consecutive business dates (Sunday-Thursday) starting and ending with specific days.
Can this be done automatically or I have to this manually?
0
votes
0answers
10 views
Change entire sheet information based on input of one cell
I'm trying to create a function on a secondary sheet to monitor which of my blogs are getting the most traffic. Currently my secondary sheet has a formula that works to show me blogs that have over ...
0
votes
2answers
16 views
How can I make a formula ignore a blank cell in Google sheets
I am creating a budget template and have a formula that multiplies cells. However, not every cell will have a number in it. How do I make it so the formula will ignore blank cells or make them = 1 ...
0
votes
0answers
18 views
Utilize SI units in Google Sheets
Seven years ago this was asked, are there any new developments with regard to formatting with SI units?
Normally, I will adjust the format to be all milli or micro simply by division, however my ...
0
votes
1answer
29 views
Google sheets spreadsheet will not format date correctly
I have a Google spreadsheet with a date column formatted to dd mmm yyyy. Many of the dates are formatted this way, but some cells will not format this way, and no matter what I select under "Format", "...
0
votes
1answer
20 views
Simplify fractional result of custom number format
In Google Sheets I would like to express any decimal number to the nearest 32nd of an inch. If I type 1.537 I want the result to be 1 17/32, but if I type 1.5 I need it to be expressed as 1 1/2 not 1 ...
0
votes
0answers
50 views
Validation of cell values
I have a problem (like everyone here, obviosly). In the sheet in the column E from row 2 (row 1 is the heading) I have to choose some text: Text1, Text2... Depending on the choice of value (it should ...
0
votes
1answer
120 views
Show insignificant zero
I want, when I write a Number in a Cell, that the zeros are still visible. In a Google sheet.
e.g. when I write 1.050, or 1.0 or 1.2000 there should stay exactly that.
Not 1.05; 1; 1.2 or something ...
0
votes
1answer
917 views
How to format a negative number red within a mixed formula result
We use the following formula:
="Stu/W: " & TEXT((A$2/B2),"[Red]#,###,##0.00") &"€"
and wish to have the resulting negative currency numbers colored red and positive blue.
Unfortunately ...
-1
votes
1answer
13 views
How can I search an argument in GSheets to return TRUE if the argument references another cell?
Hello! In some cells in the True Price Column, I applied a discount by referencing the cell Conv!C2. For example, the argument in D13 is =MROUND((D8 * 2) * Conv!C2, 5) where (D8 * 2) is me calculating ...
