CODEWITHSUNDEEP
excelexcel-formulaformattingformula
Nika Dolidze
Nika Dolidze

Reputation: 3

Look at American and European number formats and convert them into American format

Data outlay

I want to tell excell to look at American and European number formats, and return it in American format. So leave the American format as is, and convert European into American format. How would I do that? I have a 10,000 row data, so Iobviosly wouldn't do it manually.

The formulas have to be combination of, iserror, if, and whatever else necessary. I tried to use =Numbervalue(weightofgoods,","".") and it returns in the same format, except the numbers are wrong.

Upvotes: 0

Views: 1824

Answers (1)

teylyn
teylyn

Reputation: 35935

The question is "why do you have two different number formats in Excelin the first place"?

It's quite obvious that these were not manually entered (because they would have the correct format if they had been), so you must have imported them. And there must have been at least two different sources, one for each format.

You can set the decimal separator in the import wizard in Excel, or if the data does not go through the import wizard, select all the data with the "wrong" decimal character and run it through the Text to Columns tool. In step 3, click Advanced and you can set which decimal character is used in the data source. Excel will then apply the correct decimal character according to your regional settings.

enter image description here

Upvotes: 1

Related Questions