0

I have texts that are in format 444444.444444, it is meant to be a plain text. Google Sheets detects it as number and shows only 0-1 decimals. I click Format -> Number -> Plain text, the result is the formatted number as text, i.e. without all the decimals. I found out that if I edit ' in front of the number, then format it to plain text, then remove the ', it formats it correctly. But I absolutely do not want to do this for each and every cell. I also can't click "Increase decimal places", because that adds zeros after all the numbers explicitly there, and I cannot know were the zeros there in the original text or not.

Preferably, I want a solution where I can click "do not format anything in this sheet in any way". If that's not possible, even doing it for one column would suffice.

Edit: I have imported the data from Google Analytics. An example sheet is here, perform the steps above to see the error.

Improve this question
4
  • You said, "I have texts..." At what point were these entries "text"? You said "Google Sheets detects it as a number..." It's unclear from your post if these values were manually entered into the sheet (and you just wish they would be text), or if they are coming in from some source. If the latter, what source and how (e.g., form submissions, IMPORT functions, copy and paste from the internet)? All of this matters—and we can't determine any of that from your post. Consider sharing a link to the actual spreadsheet. That is likely the only way the volunteers here will be able to tell you more.
    – Erik Tyler
    Mar 31, 2022 at 10:48
  • It doesn't happen to me, did you have ALL the column selected while applying formatting as text? BTW I edited your sheet, try it now
    – Daniele
    Mar 31, 2022 at 19:10
  • @Daniele The exact steps: 1) Write a number that has 8 numbers before and after the decimal, 2) Verify the text has only 2 decimals visible, 3) While the cell is focused, click "Format" on the top bar, 4) Click "Number", 5) Verify "Automatic" is selected, 6) Click "Plain text" 7) The text has only two decimals. What could be different in your test? Something in the environment? Version?
    – Boat
    Apr 1, 2022 at 5:44
  • the column must be formatted to plain text BEFORE importing...can you give us the link and the formula with which you import?
    – Daniele
    Apr 1, 2022 at 8:22

2 Answers 2

Reset to default
0

Format the whole column as Format > Number > Plain text before you start entering those long numbers there.

Improve this answer
2
  • That is not possible, because I'm exporting the data from Google Analytics.
    – Boat
    Apr 1, 2022 at 7:34
  • It would have been pertinent to mention that in the original question. Please update the description of steps to reproduce the issue.
    – doubleunary
    Apr 1, 2022 at 15:10
0

Assuming that column A is the one in which the imported values ​​arrive, try to put this formula in a free column

=arrayformula(if(A1:A<>"",T(A1:A),))

or, better, modify the import formula accordingly

P.S. Try this one too:

=arrayformula(if(A1:A<>"",REGEXEXTRACT(A1:A,"[0-9.]+"),))

examples in your sheet

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.