98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Converting number to TEXT & 14 character
Converting number to TEXT & 14 character
Resolved · Urgent Priority · Version 2010
Thu 2 Mar 2017, 10:59Delegate Stephen said...
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
Thu 2 Mar 2017, 18:46Trainer Richard said...
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
Wed 8 Mar 2017, 14:21Delegate Stephen said...
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
Wed 8 Mar 2017, 16:03Trainer Jens said...
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
Wed 15 Mar 2017: Automatically marked as resolved.
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Create a unique items table from a duplicating table1. Ensure that your list has column headings |
