Translate from Euro # formatting ( I think ) to US formatting

You can use find and replace. Replace"." with",".
There is european format(uses , for decimals and . for grouping digits

The crazy part is there is no "." in the cell per Excel to replace.

Other things I have tried

=if(len(d2)<4,(d2*1000),d2 - works great except in the case of 20.000 it recognizes the "len" of that to be 2.
=search(".",d2) yields 2, but in the case of d12 (20.000) it provides a value error.
=right(d12,4) to see if it will provide the ".000" after 20.000 and work from there, but it provides 20.

It just seems to follow no rules and I almost think the data is corrupted except it does make sense when you see what the actual value is, but there seems to be no rule I can apply that is accurate. I cannot multiply them all by 1000 as d10 is actually supposed to be 300, but d11 is supposed to be 1722 and d12 is supposed to be 20000.

If it helps it came out of an SAP system.

Perhaps I am picking at nits, but you talk about changing formats, but what I see you describing in your sample file is changing actual numbers/values. Examples ("." as decimal separator):

A3=1.25 B3=1250
A4=470 B3=470 (so no change here)
A7=5 B7=5000
A15="15.272.714" (a text string) B3=15272.71(4)

Number formats in column A are mixed. Sometimes you have #,##0.000 and sometimes it's #,##0. The only pattern I see in your desired is you want all of those where the number format code is #,##0.000 to be multiplied by 1000. Number format, unfortunately, is not an easy property to access with spreadsheet functions.

Because all of the values (except A15) are numbers, I don't know that text manipulation functions (like REPLACE()) are going to work here. The only pattern I can see in your example is that all of the values you want to change (to multiply by 1000) are the "small" numbers. I don't know what would be a rigorous value, but something like =A3*IF(A3<100,1000,1) would work for all except A15.

For A15 where it appears that you want to simply eliminate the first ".", Something like =VALUE(SUBSTITUTE(A15,".","",1) will work, but that is based on only one example of a text string in your sample file.

If you must do both inside of one single formula, you could nest the two formulas inside of an IF() function that tests for text/number and chooses accordingly. =IF(ISTEXT(A3),VALUE(...),A3*IF(...)).

Of course, from there you will need to apply the desired number formatting to get the values to display as you want them.

Where are these data coming from? How are you importing them into Excel? Any chance the data start out as text in a text file (or similar) and you are importing that text into Excel? If so, this might be easier to fix while the data are still text, before they are brought into Excel and converted to numbers. That depends entirely on what your import/entry process is, which you have not shared.

Originally Posted by Chrisnelsonusa

If it helps it came out of an SAP system.

In this case you have two options:
1. Change regional settings on your computer to match the settings of SAP system or
2. try to export from SAP in .csv or .txt not ,XLSX and use Excel import feature (Get& Transform i.e. Power query) to import data in the correct format