RIGHT and MID function to get Initials from a Text Cell

David Noël

I need to have a formula to look at a column (Bank Name) and give back the first letters of every word in the cell.

For example if Bank Name is First National Bank of Atlanta, I would like to get back FNBOA in the Naming Convention column.

Brian_Richardson

Smartsheet has limited text parsing capabilities, so this is complicated! But doable.

In lieu of creating a massive, massive formula that will be completely unreadable and unfixable, I broke out multiple columns to parse the text into blocks that could be combined.

You will need to decide how many possible "blocks" of words you want to deal with. You can set any number, I chose 5 as I figured most banks won't have more than 5 words in their name, but you can do whatever you like. Just extend the pattern for as many or few blocks as you like.

The methodology I used is this:

1. Find the first word by searching for a space and using LEFT to get the letters before the space
2. Find the remainder of the text using MID, starting at the position of the first space and continuing for the length of the text.
3. Find the second word in the name by checking first to see if there's still a space left in the remainder of the text from step 2. If so, use LEFT to get the second word up to the next space. If there's not a space, that means there's only two words in the name, so the second word is just the remainder of the text from step 2.
4. Find the remainder of the text after the second word by using MID to search the remainder from step 2.
5. Rinse and repeat as many times as needed
6. Also wrap all formulas in IFERROR so that, when the words "run out" you don't get errors, just spaces.
7. Finally, use a simple series of LEFT formulas to pull the first word from each "block" of the name.

Here's the columns and formulas. All columns are Text/Number columns.

Name: contains the full name of the bank

1st block:

=IFERROR(LEFT(Name@row, FIND(" ", Name@row)), "")

1st remaining:

=IFERROR(MID(Name@row, FIND(" ", Name@row), LEN(Name@row)), "")

2nd block:

=IFERROR(IF(FIND(" ", [1st remaining]@row) = 0, [1st remaining]@row, LEFT([1st remaining]@row, FIND(" ", [1st remaining]@row))), "")

2nd remaining:

=IFERROR(MID([1st remaining]@row, FIND(" ", [1st remaining]@row), LEN([1st remaining]@row)), "")

3rd block:

=IFERROR(IF(FIND(" ", [2nd remaining]@row) = 0, [2nd remaining]@row, LEFT([2nd remaining]@row, FIND(" ", [2nd remaining]@row))), "")

3rd remaining:

=IFERROR(MID([2nd remaining]@row, FIND(" ", [2nd remaining]@row), LEN([2nd remaining]@row)), "")

4th block:

=IFERROR(IF(FIND(" ", [3rd remaining]@row) = 0, [3rd remaining]@row, LEFT([3rd remaining]@row, FIND(" ", [3rd remaining]@row))), "")

4th remaining:

=IFERROR(MID([3rd remaining]@row, FIND(" ", [3rd remaining]@row), LEN([3rd remaining]@row)), "")

5th block:

=IFERROR(IF(FIND(" ", [4th remaining]@row) = 0, [4th remaining]@row, LEFT([4th remaining]@row, FIND(" ", [4th remaining]@row))), "")

5th remaining:

=IFERROR(MID([4th remaining]@row, FIND(" ", [4th remaining]@row), LEN([4th remaining]@row)), "")

Initials:

=IFERROR(LEFT([1st block]@row, 1) + LEFT([2nd block]@row, 1) + LEFT([3rd block]@row, 1) + LEFT([4th block]@row, 1) + LEFT([5th block]@row, 1), "")

After you setup all these columns and formulas, you should be able to get results like this:

First National Bank of America becomes FNBoA

Brian_Richardson

Smartsheet has limited text parsing capabilities, so this is complicated! But doable.

In lieu of creating a massive, massive formula that will be completely unreadable and unfixable, I broke out multiple columns to parse the text into blocks that could be combined.

You will need to decide how many possible "blocks" of words you want to deal with. You can set any number, I chose 5 as I figured most banks won't have more than 5 words in their name, but you can do whatever you like. Just extend the pattern for as many or few blocks as you like.

The methodology I used is this:

1. Find the first word by searching for a space and using LEFT to get the letters before the space
2. Find the remainder of the text using MID, starting at the position of the first space and continuing for the length of the text.
3. Find the second word in the name by checking first to see if there's still a space left in the remainder of the text from step 2. If so, use LEFT to get the second word up to the next space. If there's not a space, that means there's only two words in the name, so the second word is just the remainder of the text from step 2.
4. Find the remainder of the text after the second word by using MID to search the remainder from step 2.
5. Rinse and repeat as many times as needed
6. Also wrap all formulas in IFERROR so that, when the words "run out" you don't get errors, just spaces.
7. Finally, use a simple series of LEFT formulas to pull the first word from each "block" of the name.

Here's the columns and formulas. All columns are Text/Number columns.

Name: contains the full name of the bank

1st block:

=IFERROR(LEFT(Name@row, FIND(" ", Name@row)), "")

1st remaining:

=IFERROR(MID(Name@row, FIND(" ", Name@row), LEN(Name@row)), "")

2nd block:

=IFERROR(IF(FIND(" ", [1st remaining]@row) = 0, [1st remaining]@row, LEFT([1st remaining]@row, FIND(" ", [1st remaining]@row))), "")

2nd remaining:

=IFERROR(MID([1st remaining]@row, FIND(" ", [1st remaining]@row), LEN([1st remaining]@row)), "")

3rd block:

=IFERROR(IF(FIND(" ", [2nd remaining]@row) = 0, [2nd remaining]@row, LEFT([2nd remaining]@row, FIND(" ", [2nd remaining]@row))), "")

3rd remaining:

=IFERROR(MID([2nd remaining]@row, FIND(" ", [2nd remaining]@row), LEN([2nd remaining]@row)), "")

4th block:

=IFERROR(IF(FIND(" ", [3rd remaining]@row) = 0, [3rd remaining]@row, LEFT([3rd remaining]@row, FIND(" ", [3rd remaining]@row))), "")

4th remaining:

=IFERROR(MID([3rd remaining]@row, FIND(" ", [3rd remaining]@row), LEN([3rd remaining]@row)), "")

5th block:

=IFERROR(IF(FIND(" ", [4th remaining]@row) = 0, [4th remaining]@row, LEFT([4th remaining]@row, FIND(" ", [4th remaining]@row))), "")

5th remaining:

=IFERROR(MID([4th remaining]@row, FIND(" ", [4th remaining]@row), LEN([4th remaining]@row)), "")

Initials:

=IFERROR(LEFT([1st block]@row, 1) + LEFT([2nd block]@row, 1) + LEFT([3rd block]@row, 1) + LEFT([4th block]@row, 1) + LEFT([5th block]@row, 1), "")

After you setup all these columns and formulas, you should be able to get results like this:

First National Bank of America becomes FNBoA

David Noël

Thank you Brian, all of that did work. I was thinking it would be more contained than this but again, it does work.

Brian_Richardson

Heh, to contain it all in one formula would honestly result in a formula that is impossible to read or understand, or troubleshoot when something broke. If you want to try, though, you'd start with the Initials formula, and replace the [1st block]@row with the formula for that column. Repeat for each "block" formula. Then you'll notice that those formulas reference the "remaining" columns, so replace those references (like [2nd remaining]@row) with the formula for that column. Rinse and repeat until you have no more @row references other than the Name@row.

David Noël

I think I follow but not 100% sure; I'll play around with it. If you happen to give it a go, I'd love to see it.

Brian_Richardson

It's actually too long to fit in one cell, you have to split it up to make it work