Results 1 to 9 of 9

Thread: Converting number to fixed length field

  1. #1
    Board Regular
    Join Date
    Jul 2019
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Converting number to fixed length field

    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 Cell B1
    Value in Data Cell 12345,67
    Result Cell C1
    Formula in Result Cell =REPT(0,14-LEN(TEXT(B1,"0.00")))&TEXT(B1,"0.00")


    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!

  2. #2
    Board Regular
    Join Date
    Jun 2005
    Location
    London
    Posts
    7,761
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Converting number to fixed length field

    Not the most efficient, but does the job...
    PHP Code:
    =TEXT(INT(B1)&IF(B1<>INT(B1),RIGHT(ROUND((B1-INT(B1)),2),2),""),"00000000000000"
    Neil

  3. #3
    Board Regular
    Join Date
    Jul 2019
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Converting number to fixed length field

    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.

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,032
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Converting number to fixed length field

    I think you are overcomplicating this unnecessarily.
    Just multiply your original number by 100, and zero pad that.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    Board Regular
    Join Date
    Jun 2005
    Location
    London
    Posts
    7,761
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Converting number to fixed length field

    If I've understood, it's much simpler than I realised. Try this:
    =TEXT(B1*100,"00000000000000")
    Neil

  6. #6
    Board Regular
    Join Date
    Jul 2019
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Converting number to fixed length field

    Quote Originally Posted by Joe4 View Post
    I think you are overcomplicating this unnecessarily.
    Just multiply your original number by 100, and zero pad that.


    Yes I was!

  7. #7
    Board Regular
    Join Date
    Jul 2019
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Converting number to fixed length field

    Quote Originally Posted by njimack View Post
    If I've understood, it's much simpler than I realised. Try this:
    =TEXT(B1*100,"00000000000000")
    Thats the one!

  8. #8
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,645
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Converting number to fixed length field

    How about = RIGHT("00000000000000" & SUBSTITUTE(A1,".",""), 14) which handles any location of the decimal point.

  9. #9
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,032
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Converting number to fixed length field

    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 by Joe4; Oct 18th, 2019 at 12:35 PM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •