35

Since @jam 's answer there might be some improvement in Spreadsheet's features. If you'd like to preserve and use the numbers as figures (e.g., automatic fill) you might apply the settings from the Format > Number > More Formats > Custom number format... menu, and set the pattern to 000, where the number of zeros represent the number of digits to ...


30

The button to change the text wrap option is slightly hidden. Hence, adding a screenshot from the new Google Sheets UI.


11

Here is a regex based solution: = regexreplace( "Text", "(.)", "$1"&char(10) ) Translation: Look in the input "Text", which could be a cell reference too, and find each instance of the regular expression ., i.e., each single character. Store the matched character in a sub-expression so we can use it later by putting the regex within parentheses. Then ...


11

Google Spreadsheets uses 'грн.' to identify hryvnia. To use the currency Google tells us we need to: Select the range of cells you'd like to format or modify. Click the 123 toolbar icon. Select the number, date, or currency format you'd like to apply to the range of cells. You can also add Custom Currencies (like ₴) using the 123 menu as in the screenshot ...


10

The way to address the problem is to use a combination of custom function in google spreadsheet and google script. I will use examples to try to address solution for your problem Formula protection : My understanding is you will have users enter new entries into a new row. This means you could apply the formula automatically to this new row, which might ...


8

A quicker way of formatting a cell as text, is to add a preceding apostrophe to your number. In a cell, type '050 and it will be interpreted as the string 050. Another option is to write it as a formula: ="050"


6

I figured out a way that is almost as good. Early on in Google sheets double clicking the paintbrush icon worked. Why Google removed that I don't know. But while you can't make the paintbrush icon "stick" anymore, what you can do is: click the cell (or range of cells) whose format you want to copy. click the paint-format paintbrush icon (to copy format). ...


5

As of 22 February 2017, support for rotated text will be introduced: Note Gradual rollout (potentially longer than 3 days for feature visibility) Reference Blog post reference: Support for rotated text in Google Sheets


3

A1: =ARRAYFORMULA(JOIN("-",TEXT({"7/22","7/28"}+(COLUMN(A1)-1)*7,"M/DD"))) Drag to right. Two dates are initialized and a column number*7 is added per each column. TEXT to format dates " m/dd" JOIN the 2 dates by -


3

Your first formula worked for me. Perhaps there's something off about the way you've formatted one of the URL's?


3

What you're looking for is conditional number formatting. You can do this in the Google doc by highlighting the column you wish to format and clicking: Format → Number → More Formats → Custom number format... Thereafter, specify this as your formula: [<10]_(###0.00_);_(#0_) What it means is, if the cell value is less than 10 then format the cell in #,#...


3

If a cell is narrow, then here is a quick and dirty approach. Type the word into the spreadsheet cell with a space between each later: V e r t i c a l T e x t Then make the width of the column narrow so that text wrapping will stack the letters vertically. V e r t i c a l T e x t This will only work if the cell has a width of one letter. If the ...


3

You can combine a couple of the approaches suggested in some other answers by creating a custom function in Tools > Script Editor: function VERTICAL_TEXT(text) { var vert_text = text.replace(/(.)/g,"$1\n"); return vert_text; } And then using it in the cell: =VERTICAL_TEXT("cell contents")


3

You have a long header cell that you have rotated to save space. However, when the height of the row is shortened, a "long" value is effectively right-aligned so that the end of the value is shown rather than the beginning. As noted by @marikamitsos, it's not possible to do what you wish. However, wrapping the text is a good work-around to display the ...


2

I needed to have a leading zero on the current month of the year for creating a custom date code I use on my invoices. This is what I did: =IF(LEN(MONTH(TODAY()))=1;CONCATENATE(0,MONTH(TODAY()));MONTH(TODAY()))


2

Until we have real vertical test, it's the simplest to leave the text orientation horizontal and spread each heading over several cells using the cell merge function, in a stairlike fashion:


2

First Select the Cell you want to Wrap From The toolbar, select "Format" -> "Text Wrapping" -> "Wrap"


2

Possibly try a different approach. Don't use query but instead, in A1 and copied across to B1: =index(IMPORTHTML("http://www.thepowerof10.info/athletes/profile.aspx?athleteid=800922&viewby=date","table",18),,column()) Then either format ColumnB as Duration (and accept some extra zeros) or also use the plain IMPORTHTML as the source to copy format only ...


2

closest you can get is to use 1+ column go to 123 or Format select Numbers select More formats select Custom number format... type: #,###.### and corect it with: =IF(RIGHT(A2;1)=".";SUBSTITUTE(A2;".";"");A2) https://docs.google.com/spreadsheets/d/


2

custom number format: [<1000]0"";[<1000000]0,"K"


2

Google sheets now supports multiple hyperlinks in one cell. Select text fragment and press ctrl+K or use hyperlink icon after selecting text.


2

To specify power of exponent, such as to powers of 3, 6, 9, etc, specify the number of places in the format. e.g. ##0.00#E+## Note that there are 3 places on the left of the decimal and 3 places on the right of the decimal. The powers will increase or decrease by multiples of 3. The exponential display can be formatted as you wish with 0 or #.


2

If you are after this then it's not possible: You can do this only manually. If you just want more visible star you can use one of these instead like: =CONCATENATE("✱", Sheet1!A1, Sheet1!B1) ✱ ★ ☆ ✢ ✥ ✦ ✧ ❂ ❉ ✯ ✰ ✲ ✴ ✵ ✶ ✷ ✸ ❇ ✩ ✪ ✫ ✬ ✭ ✹ ✺ ✻ ✼ ❈ ✮ ✡ ✾ ✿ ❀ ❁ ❃ ❊ ❋ ✣ ✤ ✽ ⁂ ⁑ ✳ ❄ ❆ ❅ ⋆ ≛ ☪


2

For now, I have worked around the issue by rotating the text down instead of up. Although this doesn't achieve the alignment aesthetic I wanted, it will suffice for now, and clipping end of value text works properly.


2

Matter is with SEARCH() returning error when failing to find your criteria. To avoid that wrap your SEARCH() in IFERROR() like this: =IF(IFERROR(SEARCH("wheel", A41),),"car", IF(IFERROR(SEARCH("banana", A41),),"fruit","No matches"))


1

like this: =IF(AND(A1>9999999; A1<=99999999); "+"&"55"&" ("&"00"&") "&LEFT(A1;4)&"-"&RIGHT(A1;4); IF(AND(A1>99999999; A1<=999999999); "+"&"55"&" ("&"00"&") "&LEFT(A1;5)&"-"&RIGHT(A1;4); )) and then "00" / "55" can be referenced from a cell for ultimate comfort


1

Looking at you example spreadsheet the calculation in seconds is done with the following formula: =86400*(if(D4-C4<0,1,0)+D4-C4) This is the breakdown of the formula: time and dates in spreadsheets are floating point numbers. D4-C4 is subtracting the start time from the end time. The if statement is cheeking if the subtraction is negative, that ...


1

Set your start and end fields to time format (Format -> Number -> Time) and your time difference calculation field to duration format (Format -> Number -> Duration). Once you have done that you can just add and subtract times like normal number to get the time difference. However, you will get a negative time if one event stretches past midnight....


Only top voted, non community-wiki answers of a minimum length are eligible