Solved Combining partial text from 2 or more cells in Excel

Microsoft Excel 2010 - complete product...
July 21, 2014 at 08:55:23
Specs: Windows 7
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!

See More: Combining partial text from 2 or more cells in Excel

Report •

July 21, 2014 at 10:01:06
✔ Best Answer
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:


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

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


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.

Report •

July 21, 2014 at 11:38:05
Here is a modification of the ideas that DerbyDad03
gave you:

With your data like this:

       A          B          C          D
1) Last Name    First       ID      Complete ID 
2) Johnson     Michael    
3) Public      John       
4) Smith       Alfred     
5) Nee         Michael    

In Column C enter the formula:


In column D enter the formula:


You should end up with something like:

       A          B          C          D
1) Last Name    First       ID      Complete ID 
2) Johnson     Michael    johnsmi   johnsmi01
3) public      John       publijo   publijo01
4) Smith       Alfred     smithal   smithal01
5) Nee         Michael    neemi     neemi01

This should work for 99 duplictes and if you wish
you can hide colomn C.


message edited by mmcconaghy

Report •

Related Solutions

Ask Question