CODEWITHSUNDEEP
google-sheetsgoogle-apps
vasilyrud
vasilyrud

Reputation: 855

How to prevent Google Spreadsheet from interpreting commas as thousand separators?

Currently, pasting 112,359,1003 into Google Sheets automatically converts the value to 1123591003.

This prevents me from applying the Split text to columns option as there are no commas left to split by.

Split text into Columns image

Note that my number format is set to the following (rather than being Automatic):

Number format option selected

Selecting the Plain text option prevents the commas from being truncated but also prevents me from being able to use the inserted data in formulas.

The workaround for this is undesirable when inserting large amounts of data: select cells that you expect to occupy, set to Plain Text, paste data, set to back to desired number format.

How do I disable the automatic interpretation by Google Spreadsheet of the commas in my pasted numeric values?

Upvotes: 5

Views: 9596

Answers (2)

TheMaster
TheMaster

Reputation: 50697

  • TEXT formatting:
    • Use ' to prepend the number. It'll be stored as text regardless of actual formatting.
    • Select the column and set formatting as `plain text.
    • In both the above cases, You can multiply the resulting text by 1 *1 to use in any formula as a number.

  • NUMBER formatting:

    • Keep Number formatting with ,/Automatic.

    • Here, though split text to columns might not work, You can use TEXT() or TO_TEXT() 

      =ARRAYFORMULA(SPLIT(TO_TEXT(A1:A5),","))

Upvotes: 2

Webber
Webber

Reputation: 5514

You can not paste it in any number format, because of the nature of numerical format types. It will parse it into an actual number and physically store it in this format. Using plaintext type, like you are, is the way to go for this.

However, there are some options to perform these tasks in a slightly different way; - you might be able to use CSV-import functionality, which prevents having to change types for a sheet. - you can use int() function to parse the plaintext value into an int. (and combine this with lookup functions).

Upvotes: 3

Related Questions