What is the easiest way to insert data with the wrong decimal separator into Excel?

Question

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?

Answer 1

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