Results 1 to 4 of 4

Thread: how would i change these cells?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Oct 2009
    Posts
    768
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default how would i change these cells?

    I have a spreadsheet of store numbers that I need to change around. The first 3 characters are always ABC, and then it follows with 2-4 digits. I need to make these all 4 digits, with the missing characters being made 0. So for example ABC1234 would stay as is, ABC123 would become ABC0123, and ABC12 would become ABC0012.

    How would I do this?

  2. #2
    Board Regular lrobbo314's Avatar
    Join Date
    Jul 2008
    Location
    California
    Posts
    2,403
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: how would i change these cells?

    Here is a UDF that seems to do what you are looking for.

    Code:
    Function FormatAlpha(s As String)
    Dim L As String: L = left(s, 3)
    Dim R As String: R = Replace(s, L, "")
    FormatAlpha = L & Format(R, "0000")
    End Function
    To add code to a workbook. Hit Alt+F11. Hit Alt+I+M to insert new module. Then paste code.
    Array formulas must be entered by hitting Ctrl+Shift+Enter.

    We can't solve problems by using the same kind of thinking we used when we created them.

    Imagination is more important than knowledge.

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,958
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: how would i change these cells?

    With a formula
    =LEFT(A2,3)&TEXT(MID(A2,4,100),"0000")
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #4
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,209
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: how would i change these cells?

    Here is another formula solution that you can consider...

    =REPLACE(A2,4,0,REPT(0,7-LEN(A2)))
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

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
  •