computing
  • 0

Solved Combining Partial Text From 2 Or More Cells In Excel

  • 0

I am currently trying to make a spreadsheet where I assign a ID to every set of data which I have. The ID is the first 5 letters of the last name followed by the first 2 letters of the first name and a two digit number (usually 01 but can differ in order to account for duplicates). I was wondering if there was some sort of a shortcut I could use to do this instead of manually entering every single one. Below is an example of what I’m trying to do:

Last Name First Name ID
Johnson Michael johnsmi01

Thanks in advance for your help!

Share

1 Answer

  1. First, a posting tip:

    If you click on the blue line at the bottom of this post, you can read the instructions on how to post data in this forum so that the format is retained. It may take some trial and error via the Preview Follow Up button, as explained in the instructions, but it can be done. To make it easier for us to understand the layout of your data, it should look like this:

             A              B            C
    1    Last Name     First Name        ID
    2    Johnson       Michael       johnsmi01

    OK, the easy part is the creation of the basic string e.g. johnsmi01. The harder part is dealing with the duplicates.

    This will get you your basic string:

    =LOWER(LEFT(A2,5) & LEFT(B2,2)) & “01”

    The LEFT function extracts the specified number of characters from the referenced cell. The Concatenation operator (&) combines the results of the functions into one string. The LOWER function converts all letters to lower case.

    However, you need to increment the digit at the end to account for duplicates, triplicates, etc. One method for doing that is to use a Helper Column so that you can count how many times any given “partial ID” (e.g. johnsmi) occurs.

    Note: This method will work for up to 9 occurrences of a partial ID. If you will have more than 9, then things get a little more complicated, but it can still be done. Let us know if you need more than 9.

    OK, so let’s say you have the set up described above. You need to create a Helper Column, which you can then hide so that it doesn’t show. I’ll use Column A, but you can use any unused column that you want, the concept is the same.

    1 – Insert a new Column A. this will move your names to Columns B & C
    2 – Place this formula in A2 and drag it down:

    =LOWER(LEFT(B2,5)&LEFT;(C2,2))&”0″

    This will give you your partial ID without the final digit, e.g. johnsmi0

    3 – In D2, enter this formula and drag it down:

    =A2&COUNTIF;($A$2:A2,A2)

    Note the placement of the two $, they are required to lock the beginning of the range that we are counting.

    This will pickup the partial ID in Column A and append a digit representing the number of times that partial ID appears in Column A.

    Let us know if you have any questions.

    Click Here Before Posting Data or VBA Code —> How To Post Data or Code.

    • 0