an unthousand comma

  • Thread starter Thread starter ploboo
  • Start date Start date
P

ploboo

I scan a telephone bill in France that I want to analyse
but they use the format 0,076 to show 0.076. So when I
send the scan to Excel it's still displayed as 0,076. How
do I convert this to a usable number?

Thanks in advance for any help.
 
Hi Ploboo!

One way, and I'm sure there must be better, is to parse the numbers:

=IF(ISNUMBER(B23),B23/1000,LEFT(B23,FIND(",",B23)-1)+RIGHT(B23,LEN(B23
)-FIND(",",B23))/1000)

The base formula is:

=LEFT(B23,FIND(",",B23)-1)+RIGHT(B23,LEN(B23)-FIND(",",B23))/1000

That's OK until you meet 1,567 for 1.567 as that will get recognised
as a number.

So I wrap it in a test for a number and divide those by 1000

It seems to work OK.

Just another pity that we can't all get together and agree these
decimal / thousands separators.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top