Converting number to fixed length field

TheRedCardinal

Board Regular
Joined
Jul 11, 2019
Messages
55
Hi all,

Not VBA related at this stage but I will be hoping to convert it into VBA in the long run.

I need to convert a number, which will be of indeterminate length, into a text string of fixed length. The length is 14 characters long.

To fill the length, I need to add leading zeros to the start of the number. So far I have come up with this:

Data CellB1
Value in Data Cell12345,67
Result CellC1
Formula in Result Cell=REPT(0,14-LEN(TEXT(B1,"0.00")))&TEXT(B1,"0.00")

<tbody>
</tbody>


The problem is the result I get include the decimal place (fairly obviously):

00000123456.70


But I can't figure out how to get it without the decimal place:

00000012345670

I can't use LEFT and MID functions because the "." moves around and in fact in case where there is no decimal place, isn't even there.
I have tried formatting the numbers to be two decimal places but while it looks right on the screen: 1234 when formatted to 2 decimal places looks like 1234.00, but converts to 1234, and 1234.5, when formatted to two decimal places looks like 1234.50, but the Text function returns 1234.5.

Thanks!
 

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,761
Not the most efficient, but does the job...
Code:
=TEXT(INT(B1)&IF(B1<>INT(B1),RIGHT(ROUND((B1-INT(B1)),2),2),""),"00000000000000")
 

TheRedCardinal

Board Regular
Joined
Jul 11, 2019
Messages
55
Hi!

Thanks for that.

It doesn't quite work but this might be because my request was not clear.

When the number has full decimal places, it works fine, so 13663.13 becomes 00000001366313 as expected.

However 10152.00 just becomes 00000000010152 - i.e. the ".00" is ignored.
It should be 00000001015200

Likewise when having an exact 10th in the decimal, I lose all the decimals:

56462.1 becomes 00000000056462.
It should be 00000005646240.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,177
Office Version
365
Platform
Windows
I think you are overcomplicating this unnecessarily.
Just multiply your original number by 100, and zero pad that.
 

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,761
If I've understood, it's much simpler than I realised. Try this:
=TEXT(B1*100,"00000000000000")
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,697
How about = RIGHT("00000000000000" & SUBSTITUTE(A1,".",""), 14) which handles any location of the decimal point.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,177
Office Version
365
Platform
Windows
Mike,

If the entry is truly a numeric entry (maybe with formatting to show decimals):
I need to convert a number, which will be of indeterminate length,
Then your code wouldn't work in this case:
However 10152.00 just becomes 00000000010152 - i.e. the ".00" is ignored.
It should be 00000001015200
Now, if 10152.00 was entered as Text, then it would work...
 
Last edited:

Forum statistics

Threads
1,078,447
Messages
5,340,345
Members
399,370
Latest member
salamon

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top