Formatting Currency from 4.283,88 to 4,283.88 for example... HELP HELP HELP

[cory0789]

OK,

So the file that gets emailed to me puts all of the currency numbers in a format as stated so 575,00 should be 575.00 and 1.285,22 should be 1,285.22 can anyone tell me the easiest way to format the entire spreadsheet to the correct formatting!!! I have tried what I know and cannot get it to work.

currency.xlsm

[cory0789]

Nobody... knows

[Mr_HiB]

Whoever gave you the file - they didn't use any formatting to their numbers.

They literally typed in $ 4,427.50 = the actual currency symbol with a space in between and then the , and the . in the amount... so it's not a true number where we can change the formatting....

You can also prove this out by trying to add a number to the value for example A2+1 see what you get for an answer... it'll be an error... again since the column A is not numbers... they're text because of the way they hand typed the formatting in the cell....


You'll notice this especially if you do a text to column.

So I did a text to column to break it all apart and the reassemble it again. I guess we could have used the text functions, mid, left and right coupled with some find to give us the exact break downs.... but figured text to columns is quicker and more precise.

See attached.

Hilmy

[CARACALLA]

Hi

Format column B currency or valute

B2 =IF(LEN(A2)=10,SUBSTITUTE(SUBSTITUTE(A2,".",","),",",".",2),SUBSTITUTE(A2,",","."))+0 copy down

[FDibbins]

Slightly shorter version....
=--SUBSTITUTE(SUBSTITUTE(MID(A2,3,99),".",""),",",".")

[Mr_HiB]

Doh.. Lol nice job