EXCEL FORMULA

Hello

I am look for a way in Excel to convert a String column-A (fix lenght of 8 characters) to number column-B as follow

COLUMN-A (fix lenght of 8 char)      COLUMN-B (number)

12345678   will give           123456,78
00000006 will give                         0,06
00034434 will give                    344,34
00000011 will give                        0,11

Thank you for your help
Rgds
gadsadAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
johnb25Connect With a Mentor Commented:
If text in Col A
=ROUND(VALUE(A1)/100,2)

John
0
 
helpfinderIT ConsultantCommented:
try this one
=LEFT(A1,6)&","&RIGHT(A1,2)
0
 
gadsadAuthor Commented:
Very nice, but for 0,06 it displays 000000,06 (it keeps the 8 characters even for numbers)
Any way to change it afterwards  to 0,06 maybe?
Thanks
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
johnb25Commented:
@ helpfinder, I think the ask was for a number, your formula is Text.
I am taking the , to be a European decimal format.

John
0
 
gadsadAuthor Commented:
Sorry I do not understand your answer... what is @helpfinder?

Yes the , is indeed the European decimal format

Thanks
0
 
helpfinderIT ConsultantCommented:
then try to remove leading zeros from my result by multiple it with 1 in next cell - see attachment
you can have additional columns then, or just to hide helping columns
sample.xlsx
0
 
gadsadAuthor Commented:
perfect
Thank you
0
All Courses

From novice to tech pro — start learning today.