Results 1 to 8 of 8

Thread: Running Numbers Formula

  1. #1
    New Member
    Join Date
    Nov 2018
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Running Numbers Formula

    hello.
    Hope You will be Fine.
    My Question is that I Create a Table That Has Column as below


    ID NAME CITY
    AD-01 ABC UVW
    AD-02 EFG XYZ
    (????) <--Here i want the formula that change the only NUMBERS not TEXT automatically.
    e.g. AD-03

  2. #2
    Board Regular
    Join Date
    Sep 2016
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    1 Thread(s)

  3. #3
    New Member
    Join Date
    Nov 2018
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Running Numbers Formula

    ID NAME CITY
    AD-01 ABC UVW
    AD-02 EFG XYZ
    (????) <--Here In ID Column i want the formula that change the only NUMBERS (XX-03) not TEXT.
    e.g. AD-03
    This Suggested Solution(https://www.mrexcel.com/forum/excel-...e-numbers.html) is doesn't match or work on my request

  4. #4
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    629
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Running Numbers Formula

    Are you trying to increment the AD-nn ID in the next row?

    such as this in A4:

    Code:
    ="AD-"&TEXT(1+RIGHT(A3,LEN(A3)-FIND("-",A3)),"00")
    or

    Code:
    =LEFT(A3,3)&TEXT(1+RIGHT(A3,LEN(A3)-FIND("-",A3)),"00")
    Or you need to provide more examples re what you want.
    Last edited by kweaver; Jul 17th, 2019 at 12:46 PM.

  5. #5
    New Member
    Join Date
    Nov 2018
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Running Numbers Formula

    i am very very Very Thanks full to you.
    it work as i want.
    you are my hero

    Thanks

  6. #6
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    5,146
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Running Numbers Formula

    Hi,

    If I understand correctly, you don't need any formula for what you want.

    With AD-01 in A2, Select A2, Left click and hold down on the cell Fill handle (little square in lower right corner of A2) and drag down Column, the Numbers with auto increment leaving the AD intact.

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

    Default Re: Running Numbers Formula

    Here is another (fairly simple) formula that can also be considered. Put this formula in the first cell (the one you want AD-01 to appear in) and copy it down as needed...

    =TEXT(ROWS($1:1),"A\D-00")
    Last edited by Rick Rothstein; Jul 17th, 2019 at 01:43 PM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  8. #8
    Board Regular
    Join Date
    Sep 2016
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Running Numbers Formula

    Quote Originally Posted by Shafique View Post
    ID NAME CITY
    AD-01 ABC UVW
    AD-02 EFG XYZ
    (????) <--Here In ID Column i want the formula that change the only NUMBERS (XX-03) not TEXT.
    e.g. AD-03
    This Suggested Solution(https://www.mrexcel.com/forum/excel-...e-numbers.html) is doesn't match or work on my request
    I think it does - Rick Rothstein has provided the same solution to that thread and this thread.

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
  •