Find n-th space from the right...

Another quick post:

Have you ever had the wonderful pleasure of needing to pull all characters to the right of the 2nd or 3rd space within the string?  I know I have..

Solution:
*Disclaimer*: I have no idea where I found this beauty, but again, I FOUND it because someone else was willing to share - and sharing is the whole point of this blog...  I'm posting this just as much for myself as for anyone else. :)

Assuming your string is in cell C2:

=FIND(CHAR(1),SUBSTITUTE(C2," ",CHAR(1),LEN(C2)-LEN(SUBSTITUTE(C2," ",""))-n))

For n:
("n" determines the number of spaces)
0=1
1=2 etc...

Concatenation and Searching for text in a string

Today I was asked to create a unique identifier by obtaining the initials of a name and adding on the last 4 digits of a specific encounter number.  This required the following functions:

Left(text, [num chars])
Mid(text, start_num, num_chars)
Right(text, [num_chars])
Search(find_text, within_text, [start_num])
and the symbol used to concatenate values/strings: "&"

Here's the dataset:

Here we have the names list by LastName,FirstName and the ID#'s.

The assignment: "Provide Initials (First then Last) and the last 4 digits in the ID#"

**For this example, we're going to assume all of the names are in the exact same format down every row.

Let's Begin:

In Cell C1 we'll provide a column header of "New ID".  In cell C2 we'll go to the formula bar and begin with our formula.  Since the First initial is in the MIDDLE of the text string and and be found after a comma, we'll use the Mid() and Search() formulas here.

For the MID() formula we need to assign values to the following requirements -

• text: the text we're referencing is the Name column and found in cell A2
• start_num: we need to search for the "," and start with the character immediately after it (+1)
• Search requires the following to be addressed:
• Find_text or Text to search for: "," (include the "" around the comma in the formula for this one)
• within_text: cell A2
• [start_num]: "1" - this is the first character in the cell A2
• num_chars: 1 - we only want the first letter of the first name

So, now that we have this written out in words, let's convert it to the formula:

=MID(A2,SEARCH(",",A2,1)+1,1)

This returns "S" in cell C2.  Next, we need the First letter of the Last Name.  This one is a bit more simple since it is the beginning letter of the text string.  We'll use the LEFT() formula

For the LEFT() formula, we need to assign values to the following requirements:

• Text: A2
• [num_chars]: 1

This will provide the first character from the left using the value in cell A2.  The formula looks like this:

=LEFT(A2,1)

So, we want all of this in one cell, right?  How do we enter more than one formula?  Concatenation! 

All we have to do is combine the two formulas by replacing the "=" at the beginning of the second formula with "&" and append it to the end of the first formula:

=MID(A2,SEARCH(",",A2,1)+1,1)&LEFT(A2,1)

This now returns "SD" in cell C2.  Last, we need the last four numbers in the ID#:  We'll use the RIGHT() formula to accomplish this task:

For the RIGHT() formula, we need to assign values to the following requirements:  

**Instead of looking at the left of the text, this formula starts at the right**

• Text: B2 - not A2, because the numbers are in B2!
• [num_chars]: 4

This will provide the first four characters from the right using the value in cell B2.  The formula looks like this:

=RIGHT(B2,4)

Again, we want all of this in one cell, right?  Let's concatenate by replacing the "=" with "&" and appending it to the end of the formula we've built so far:

=MID(A2,SEARCH(",",A2,1)+1,1)&LEFT(A2,1)&RIGHT(B2,4)

This now returns "SD3456" in cell C2.  Let's look back at the assignment: "Provide Initials (First then Last) and the last 4 digits in the ID#"

Now that we know we've accomplished what we wanted, we can copy the values down the column and click SAVE!

The final result:

AND the final result showing the formulas used:

The task I had to accomplish was a bit more complicated involving dates of service as well.  The last step that you would want to do is validate that the "New ID" was truly unique across your data set.  To accomplish this, you could simply create a pivot table, add the "New ID" and then Name as the Row values and add New ID to the values section as a count of New ID.  This would quickly show if you have any duplicates by listing multiple names under the same ID.

Hope you enjoyed this tutorial!  Instead of offering to donate lots of your money to me, let other people do it by clicking on an advertisement to the right!