Stephen has attended:
Excel Intermediate course
Excel Advanced course
Converting number to TEXT & 14 character
Hi , we have a IT requirement that EXCEL number inputs are converted and FORMATTED to TEXT , and we need to ensure that the character set is exactly 14
Can you advise best way to do this ?
Column A should be Column B
e.g. -1 should -1.00000000000
.5 should be 0.500000000000
-.375 should be -0.375000000000
1 should be 1.000000000000
Can you advise best way to achieve this ?
tks
S
RE: Converting number to TEXT & 14 character
Hi Stephen,
I would suggest that in Column B you type
=TEXT(A1,"0.00000000000")
This should format the cell in A with the customer 14 Character format you are after.
Try that solution and let me know if that's the result you were after
Kind regards
Richard Bailey
Microsoft Certified Trainer
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Converting number to TEXT & 14 character
Hi Richard , thanks for your response
that works to an extent
However , if I have a - in the rate then this will default to 15 characters .
So it works for
1.000000000000 (14 characters)
but not for
-1.000000000000 (becomes 15 characters !)
Can you advise any other workaround to ensure that we have 14 characters regardless
So for eg
e.g. -1 should -1.00000000000
1 should 1.000000000000
Tks
RE: Converting number to TEXT & 14 character
Hi Stephen,
I think it is time to get help from a If function.
=IF(D14>=0,TEXT(A1,"0.000000000000"),TEXT(D14,"0.00000000000"))
I hope this can do what you want.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
