I have a lot of situations where I need to quickly insert/import numeric data into Excel. I have no personal preference for either ',' or '.' as decimal separator. However, the data I import sometimes uses one separator, and sometimes the other.

The problem is that inserting the data with the wrong separator means that Excel interprets it as strings. This in turns means that any operator that works on numbers, doesn't work on this data.

The options I considered are:

  1. Manually switch the ',' for '.' in the input data when needed, before importing/inserting the data into Excel. This is an error-prone approach when dealing when text as well (ensuring only the separators are swapped) and feels like a finicky hack.
  2. Swapping the separator Excel uses every time. This might cause problems when I have multiple sheets open at the same time.

It seems to me it should be possible to tell Excel that for a give set of cells, the strings should be parsed as numbers in a given format. Is this possible? Or are the two alternatives I mentioned above the only options?

  • If you use a tool with grep capability to replace only commas preceded and followed by characters [0-9], the process should be easy and reliable. Notepad++ and many other text editors can do so, and the process could be scripted, too. See stackoverflow.com/questions/12126597/… – DrMoishe Pippik Apr 20 '18 at 1:59
  1. Text to columns
    • delimit by the decimal indicator of input data
  2. determine length of string to the right of the “initial” decimal place
  3. generate a decimal-formatted number using exponentiation
  4. generate the sum of decimal and “left-of-initial” decimal character, which yields the final number

For example, an input value of 1,22 or 1.22 would be split (by “Text to Columns”) into 1 and 22:

Cell data:

 A        B         C          D         E    
1        22        2          0.01      1.22

Formulas used:

1        22        =LEN(B1)   =10^-C1   =A1+B1*D1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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