+ Reply to Thread
Results 1 to 7 of 7

Adding Text Before A Number

  1. #1
    Registered User
    Join Date
    06-01-2010
    Location
    Calgary
    MS-Off Ver
    Excel 2007
    Posts
    2

    Adding Text Before A Number

    Hello,

    I have what might be a simple question for all you smart Excel users out there.
    I have a list of numbers, that goes from 1 - 3065. My issue is, I want to add the text ML before each number.

    So from numbers 1-10, I want three zeros before the number, so:
    ML0001
    ML0002
    ....and so on.

    Numbers 10-99, I want two zeros, before the number, so:
    ML0050
    ML0051
    ...and so on...

    Then numbers, in the hundreds, I only want 1 zero, then numbers in the thousands, no zeros need to be inserted, just the ML text. Does anyone know of a formula or VBA code that might work?

    thanks

  2. #2
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    Re: Adding Text, Before A Number

    Hi Icepirates, welcome to the forum.

    Do the cells need to physically contain the ML and filler zeros? Or can they just be shown that way? To just show them that way, use a custom cell format such as:

    "ML"0000

    When applied to your list of cells, they will appear as ML0001, ML1892, ML0291, etc.

  3. #3
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: Adding Text, Before A Number

    Custom format them as "ML"0000

  4. #4
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Adding Text Before A Number

    If you need the result as an actual value try in a spare column

    Please Login or Register  to view this content.

    Fill down

    Then you can Copy & PasteSpecial > Values to where you want the result

    EDIT

    Might be safer with
    Please Login or Register  to view this content.
    just in case A1 has more than 4 digits

    and/or just change the number of repeats required

    eg REPT("0",5-LEN(A1)
    Last edited by Marcol; 06-02-2010 at 05:07 AM. Reason: belt and braces

  5. #5
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: Adding Text Before A Number

    Quote Originally Posted by Marcol View Post
    If you need the result as an actual value try in a spare column

    Please Login or Register  to view this content.
    Fill down

    Then you can Copy & PasteSpecial > Values to where you want the result

    EDIT

    Might be safer with
    Please Login or Register  to view this content.
    just in case A1 has more than 4 digits

    and/or just change the number of repeats required

    eg REPT("0",5-LEN(A1)
    You could just use

    ="ML"&TEXT(A1,"0000")

  6. #6
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Adding Text Before A Number

    I still have a lot of formule to find out about yet! That's one I haven't come across until now, thanks Bob.

    In the meantime I'll just crawl back to my sookie blanket, VBa ....

  7. #7
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: Adding Text Before A Number

    Quote Originally Posted by Marcol View Post
    I still have a lot of formule to find out about yet! That's one I haven't come across until now, thanks Bob.
    This is as good a place as any to find out.

    Quote Originally Posted by Marcol View Post
    In the meantime I'll just crawl back to my sookie blanket, VBa ....
    Don't do that, you might miss something

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1