Excel CONCATENATE question

Question

I have in one column lastname and firstname (Smith John) in the same field

I would like to create an ID for each name with the following logic first two letters of lastname, first two letters of first name +a number starting 001. Ideally and this may not be possible in Excel I would like it to auto increment the number if there is one existing in the column.

Anyone has any suggestion?

Answer 1

Here is a potential solution:

=UPPER(CONCATENATE(LEFT(A1,2),MID(A1,SEARCH(" ",A1)+1,2),TEXT(ROW(B1),"000")))

In more detail, I'm assuming that the names are in column A and the IDs go in column B. UPPER converts to uppercase. The rest of it:

CONCATENATE(LEFT(A1,2),         #first two letters of last name
    MID(A1,SEARCH(" ",A1)+1,2), #first two letters of first name
    TEXT(ROW(B1),"000")         #ID number with leading zeroes
)

To find the first name it takes whatever comes after the first space. This isn't foolproof because some people have spaces in their last name.

To generate the ID number it just takes the current row number. Not a great solution but I can't suggest anything more specific without knowing more about what your needs are.

Answer 2

Try something like:

=MID(A1,1,2)&MID(A1,FIND(" ",A1)+1,2)&TEXT(ROW(),"000")

If A1 = Smith John Then B1 = SmJo001

Answer 3

The auto-numbering can be accomplished by counting the number of times the ID exists already. If you can spare two columns (you can hide them if you don't want to keep them), here's a solution, which produces the following table:

      A               B           C        D
 1  Name           ID begin    ID end   ID
 2  John Smith      JOSM       1        JOSM001
 3  John Smash      JOSM       2        JOSM002
 4  Jacob Black     JABL       1        JABL001

• Formula in B2: =UPPER(CONCATENATE(LEFT(A2,2),MID(A2,SEARCH(" ",A2)+1,2)))
  This will take the first 2 letters of each of the first and second words in the name column. SEARCH(" ",A2)+1 finds the index of the first character after the first space in A2.

• Formula in C2: =COUNTIF(B$2:B2,UPPER(CONCATENATE(LEFT(A2,2),MID(A2,SEARCH(" ",A2)+1,2))))
  This will count the number of IDs (calculated from the formula above) which exist up to and including this row. Note the absolute starting row for the range in COUNTIF, which makes this possible.

• Formula in D2: =CONCATENATE(B2,TEXT(C2,"000"))
  This takes the two parts of the ID, formats the number as three digits and puts it together.

Then just copy (fill) the formulas down.