Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

converting number text

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Converting number to TEXT & 14 character

Converting number to TEXT & 14 character

ResolvedVersion 2010

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

Wed 15 Mar 2017: Automatically marked as resolved.

Excel tip:

Hiding a worksheet in Excel

Want to circulate an Excel workbook file but keep certain worksheets hidden from your colleagues' view?

You can do so by bringing up the sheet you wish to hide on your screen; then going to Format - Sheet - Hide.

It will not be immediately obvious that a sheet is hidden from view unless perhaps the sheet are still labelled Sheet 1, Sheet 2 etc.

To display the sheet again, you can go to Format - Sheet - Unhide on any of the other sheets in the workbook. A dialogue box will appear, allowing you to select the hidden sheet/s. Click OK to make the sheet/s reappear again.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.09 secs.