RIGHT and MID function to get Initials from a Text Cell

Options
David Noël
David Noël ✭✭✭✭
edited 06/12/24 in Formulas and Functions

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.

Best Answer

  • Brian_Richardson
    Brian_Richardson Overachievers Alumni
    edited 06/12/24 Answer ✓
    Options

    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 | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

Answers

  • Brian_Richardson
    Brian_Richardson Overachievers Alumni
    edited 06/12/24 Answer ✓
    Options

    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 | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

  • David Noël
    David Noël ✭✭✭✭
    Options

    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
    Brian_Richardson Overachievers Alumni
    Options

    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.

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

  • David Noël
    David Noël ✭✭✭✭
    Options

    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
    Brian_Richardson Overachievers Alumni
    Options

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

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!