36 votes

Google Spreadsheets automatically removes leading zeroes

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 ...
user avatar
  • 461
30 votes

Formatting a cell with word wrap without switching modes?

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

Can I protect the formatting of a Google Sheets?

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 : ...
user avatar
11 votes
Accepted

How do you "Paint Format" more than once in Google Sheets?

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 "...
user avatar
  • 136
6 votes
Accepted

How do I Specify the Scientific Notation Power in Google Sheets?

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 ...
user avatar
  • 76
5 votes

How to alter text orientation to vertical in Google Sheets?

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 ...
user avatar
4 votes
Accepted

google cell format: only 3 digits

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 ...
user avatar
  • 251
4 votes
Accepted

How to remove insignificant decimal point in Google Spreadsheets?

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;".";...
user avatar
  • 24.6k
3 votes

How do I add dates for the beginning and end of the week in one cell

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/...
user avatar
  • 3,058
3 votes
Accepted

Image in a cell with a hyperlink

=HYPERLINK("https://mail.google.com/mail/u/0/"; IMAGE("https://i.stack.imgur.com/XJ2AY.png"; 3)) demo spreadsheet
user avatar
  • 24.6k
3 votes

Image in a cell with a hyperlink

Your first formula worked for me. Perhaps there's something off about the way you've formatted one of the URL's?
user avatar
  • 131
3 votes

How can I open multiple URLs in Sheets using one cell?

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

How to alter text orientation to vertical in Google Sheets?

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,...
user avatar
3 votes

Google Sheets text rotated up, how to clip text end instead of beginning?

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 ...
user avatar
  • 4,649
2 votes

How to alter text orientation to vertical in Google Sheets?

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:
user avatar
  • 138
2 votes

Formatting a cell with word wrap without switching modes?

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

Having units in Google Spreadsheets?

I found it. Format > Number > More Formats > Custom Number Format then enter: . "lbs" If you want two decimal points then it would be: .00 "lbs" Putting whatever you want in quotes allows you to ...
user avatar
2 votes

How to change the text line height in one cell?

Google Sheets doesn't include a setting to customize the text line height. One workaround is to set vertical align to Middle and change the row height to a size big enough. NOTES: When the OP says ...
user avatar
  • 41.8k
2 votes

IMPORTHTML data is sporadically changing to incorrect format

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","...
user avatar
  • 16.7k
2 votes
Accepted

Google Sheets Custom Number Format - Unchanging #<1000

custom number format: [<1000]0"";[<1000000]0,"K"
user avatar
  • 24.6k
2 votes

How do I Specify the Scientific Notation Power in Google Sheets?

Select the cell(s) Click on Format > Number > More formats > Custom number format... On the box write something like #0.0E+00 Click Apply button Note: The leading # is to set an optional digit ...
user avatar
  • 41.8k
2 votes

Partial cell formatting with CONCATENATE in Google Sheets

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!...
user avatar
  • 24.6k
2 votes

Entering fractions verbatim in Google Sheets

Put the numerator and denominator in different cells. Use an extra column to calculate the decimal representation of the fraction (eg d2 = b2/cd) to use in calculations, and convert the original ...
user avatar
  • 7,219
2 votes

Utilize SI units in Google Sheets

Custom number formatting supports conditionals, but only two of them plus a default format. So, to display millions and billions of dollars, you can use Format > Number > More Formats > ...
user avatar
  • 6,523
2 votes

Rich text formatting in a Google Sheets cell

Rich-Text formatting is not an OPT in/out / turn on/off feature. It's always turned on. To use it: Put the cell in edit mode Type the text Select the text part to apply bold, italics, color Apply the ...
user avatar
  • 41.8k
2 votes
Accepted

Google Sheets text rotated up, how to clip text end instead of beginning?

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 ...
user avatar
  • 121
2 votes
Accepted

File import into sheets is dropping necessary leading spaces

After waiting a while and coming back to the problem, I stumbled upon a solution that's better for someone like me who knows nothing about apps script (though I'll grant that Rubén's solution did ...
user avatar
  • 314
2 votes

Multiple IF functions on a single cell

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(...
user avatar
  • 760

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