+ Reply to Thread
Results 1 to 8 of 8

Need to auto add with mix of numbers & letters

  1. #1
    Registered User
    Join Date
    05-09-2017
    Location
    Alaska
    MS-Off Ver
    Office Professional Plus 2010
    Posts
    12

    Question Need to auto add with mix of numbers & letters

    I have a column that is in the format: AK2018-001

    I need a formula that will auto add as the column continues down; for example:

    AK2018-101
    AK2018-102
    AK2018-103

    I can't use (=A45+1) because of the "AK2018-" prefix.

    I am stuck and I'm hoping there's a simple solution that I've overlooked.

    Thanks in advance!

    EDIT: My list starts at AK2018-001 in column A3
    Last edited by Franchesca; 08-17-2018 at 08:24 PM. Reason: additional details

  2. #2
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,137

    Re: Need to auto add with mix of numbers & letters

    If your first string is in A1 then this is the formula for A2

    =left(A1,7) & Right(A1,3)+1

    PC locked up at that point
    Last edited by mehmetcik; 08-17-2018 at 08:27 PM.
    My Rules if you want my help:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Need to auto add with mix of numbers & letters

    With
    Please Login or Register  to view this content.
    This regular formula, copied down, creates an incrementing list:
    Please Login or Register  to view this content.
    The results for A1:A5 are:
    Please Login or Register  to view this content.
    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  4. #4
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Need to auto add with mix of numbers & letters

    Try this
    Enter in A2 and copy down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    v A
    1
    2 AK2018-101
    3 AK2018-102
    4 AK2018-103
    5 AK2018-104
    6 AK2018-105
    7 AK2018-106
    8 AK2018-107
    9 AK2018-108
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  5. #5
    Valued Forum Contributor jtakw's Avatar
    Join Date
    05-05-2018
    Location
    CA, USA
    MS-Off Ver
    2016
    Posts
    660

    Re: Need to auto add with mix of numbers & letters

    Hi,

    You don't need any formula.

    With your value AK2018-001 in A3, Select A3, grab the Cell Handle - little square at lower right corner of Cell A3 - and drag down. That's it.

  6. #6
    Registered User
    Join Date
    05-09-2017
    Location
    Alaska
    MS-Off Ver
    Office Professional Plus 2010
    Posts
    12

    Re: Need to auto add with mix of numbers & letters

    YES! This is such an easy solution and I feel a little embarrassed I didn't already know this! Thanks so much! This is awesome!

  7. #7
    Registered User
    Join Date
    05-09-2017
    Location
    Alaska
    MS-Off Ver
    Office Professional Plus 2010
    Posts
    12

    Re: Need to auto add with mix of numbers & letters

    Thanks everyone for your help with this! I love this community, it's the BEST excel advice on the internet!

  8. #8
    Valued Forum Contributor jtakw's Avatar
    Join Date
    05-05-2018
    Location
    CA, USA
    MS-Off Ver
    2016
    Posts
    660

    Re: Need to auto add with mix of numbers & letters

    Quote Originally Posted by Franchesca View Post
    YES! This is such an easy solution and I feel a little embarrassed I didn't already know this! Thanks so much! This is awesome!
    You're welcome, sometimes we tend to "Over Think" the problem and solution, it happens to me a lot.

    And just FYI, if you change the value in A3, you just need to double click on the A3 Cell Handle, and the rest of the Column will auto update.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 08-27-2014, 11:05 AM
  2. Replies: 13
    Last Post: 07-30-2014, 12:03 PM
  3. Replies: 11
    Last Post: 10-16-2013, 10:21 PM
  4. Parsing Letters and Numbers (And Letters)
    By jontran718 in forum Excel General
    Replies: 6
    Last Post: 01-12-2012, 12:55 PM
  5. Auto populate cells with numbers and letters?
    By quikrich3 in forum Excel General
    Replies: 2
    Last Post: 04-08-2011, 01:40 PM
  6. Replies: 2
    Last Post: 12-01-2010, 02:29 PM
  7. Auto generate numbers and letters as password
    By _bones_ in forum Excel General
    Replies: 1
    Last Post: 01-01-2010, 07:30 AM

Tags for this Thread

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