Formula to Abbreviate Company Names

Ayman Aboueid

I have been trying to genrate a formula to shorten company names:

1- For multi word names, extract the first letter of each word, skipping over short words like "and", "the", etc.

2- For single word names, return the first 7 characters only.

3- No spaces in output

Here is the closest I have gotten so far:

=IF(CONTAINS(" ", Client@row), JOIN(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Client@row, " and ", " "), " for ", " "), " the ", " "), " of ", " "), " in ", " "), " at ", " "), " on ", " "), " with ", " "), 1) +  IF(FIND(" ", Client@row) > 0, MID(Client@row, FIND(" ", Client@row) + 1, 1), "") +  IF(FIND(" ", Client@row, FIND(" ", Client@row) + 1) > 0, MID(Client@row, FIND(" ", Client@row, FIND(" ", Client@row) + 1) + 1, 1), ""), ""), LEFT(Client@row, 7))

However, I am still getting abbreviated shortwords in the output.

Thanks in advance for any advice.

jmyzk_cloudsmart_jp

Hi @Ayman Aboueid

Here is another solution.

https://app.smartsheet.com/b/publish?EQBCT=19d190e08e874beb88f7a46184a52804

First, I split the company or client name with spaces or " ".

Unfortunately, Smartsheet does not have the SPLIT function, with which the Nth word would be like this;

=SPLIT(Client@row," ", n)

So, I used a workaround I learned from @Paul Newcome .

[w1] =IFERROR(MID(Client@row, FIND([new_text]#, SUBSTITUTE([old_text]# + SUBSTITUTE(Client@row, " ", [old_text]#) + [old_text]#, [old_text]#, [new_text]#, 1)), FIND([new_text]#, SUBSTITUTE([old_text]# + SUBSTITUTE(Client@row, " ", [old_text]#) + [old_text]#, [old_text]#, [new_text]#, 2)) - FIND([new_text]#, SUBSTITUTE([old_text]# + SUBSTITUTE(Client@row, " ", [old_text]#) + [old_text]#, [old_text]#, [new_text]#, 1)) - 1), "")
[w2] =IFERROR(MID(Client@row, FIND([new_text]#, SUBSTITUTE([old_text]# + SUBSTITUTE(Client@row, " ", [old_text]#) + [old_text]#, [old_text]#, [new_text]#, 2)), FIND([new_text]#, SUBSTITUTE([old_text]# + SUBSTITUTE(Client@row, " ", [old_text]#) + [old_text]#, [old_text]#, [new_text]#, 3)) - FIND([new_text]#, SUBSTITUTE([old_text]# + SUBSTITUTE(Client@row, " ", [old_text]#) + [old_text]#, [old_text]#, [new_text]#, 2)) - 1), "")

(The same patter for [w3] to [w5])

Then, use the following formula to get the Abbreviation.

=IF(

    OR(

        FIND(" ", Client@row) = 0,

        AND(

            FIND(" ", SUBSTITUTE(Client@row, " ", "", 1)) = 0,

            HAS({Short Words}, [w2]@row)

        )

    ),

    LEFT(Client@row, 7),

    IF(HAS({Short Words}, [w1]@row), "", LEFT([w1]@row, 1)) +

    IF(HAS({Short Words}, [w2]@row), "", LEFT([w2]@row, 1)) +

    IF(HAS({Short Words}, [w3]@row), "", LEFT([w3]@row, 1)) +

    IF(HAS({Short Words}, [w4]@row), "", LEFT([w4]@row, 1)) +

    IF(HAS({Short Words}, [w5]@row), "", LEFT([w5]@row, 1))

)

The {Short Words} range

https://app.smartsheet.com/b/publish?EQBCT=af0b8844258b4f78a17e7d122b489846

Explanation:

1. Single-Word Names or Single Significant Word:
   • The OR function checks two conditions:​
     • FIND(" ", Client@row) = 0: Determines if there's no space in the Client name, indicating a single-word name.
     • AND(FIND(" ", SUBSTITUTE(Client@row, " ", "", 1)) = 0, HAS({Short Words}, [w2]@row)): Checks if the name has only one significant word by removing the first space and verifying if the second word is a short word.
   • If either condition is true, LEFT(Client@row, 7) returns the first 7 characters of the Client name.​
2. Multi-Word Names:
   • For names with multiple words, the formula evaluates each word ([w1] through [w5]):​
     • IF(HAS({Short Words}, [w#]@row), "", LEFT([w#]@row, 1)): Checks if the word is in the {Short Words} list. If it is, it returns an empty string; otherwise, it returns the first letter of the word.
   • The results are concatenated to form the abbreviation.

Michael Drayton

Hello Friend! I hope you like helper columns!
Helper column 1: [Client Name Cleaned]
=SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE([Company Name]@row, " and", ""), " the", ""), " of", ""),
" a", ""), " an", ""), " at", ""), " to", ""), " for", ""), " in", ""), " &", "")

Helper column 2: [FirstInitial]
=UPPER(LEFT([Client Name Cleaned]@row, 1))

Helper column 3: [SecondInitial]
=IFERROR(UPPER(MID([Client Name Cleaned]@row, FIND(" ", [Client Name Cleaned]@row) + 1, 1)), "")

Helper column 4: [ThirdInitial]
=IFERROR(UPPER(MID([Clean Name]@row, FIND(" ", [Clean Name]@row, FIND(" ", [Clean Name]@row) + 1) + 1, 1)), "")

Helper column 5: [FourthInitial]
=IFERROR(UPPER(MID([Clean Name]@row, FIND(" ", [Clean Name]@row, FIND(" ", [Clean Name]@row, FIND(" ", [Clean Name]@row) + 1) + 1) + 1, 1)), "")

Final Column Abbreviation [Company Abbrev]:
=IF(CONTAINS(" ", [Client Name Cleaned]@row), FirstInitial@row + SecondInitial@row + ThirdInitial@row + FourthInitial@row + FifthInitial@row, LEFT([Client Name Cleaned]@row, 7))

let me know how it goes. Good luck!
Michael

Ayman Aboueid

Hi Michael,

I think you got me half way there, however I am now getting more "initials" than I need if the company name has less than 4 words (or how many helpers "initial" columns I setup) in the name. So, with United States as client, output is USUS. I need a way to clear out any initials in helper columns if no words exist past a specific word count, or a way to not populate the Helper initial columns past tha stage when all words in the name have a corresponding initial. Thank you so much for looking into this and getting me so close!

jmyzk_cloudsmart_jp

Hi @Ayman Aboueid

Here is another solution.

https://app.smartsheet.com/b/publish?EQBCT=19d190e08e874beb88f7a46184a52804

First, I split the company or client name with spaces or " ".

Unfortunately, Smartsheet does not have the SPLIT function, with which the Nth word would be like this;

=SPLIT(Client@row," ", n)

So, I used a workaround I learned from @Paul Newcome .

[w1] =IFERROR(MID(Client@row, FIND([new_text]#, SUBSTITUTE([old_text]# + SUBSTITUTE(Client@row, " ", [old_text]#) + [old_text]#, [old_text]#, [new_text]#, 1)), FIND([new_text]#, SUBSTITUTE([old_text]# + SUBSTITUTE(Client@row, " ", [old_text]#) + [old_text]#, [old_text]#, [new_text]#, 2)) - FIND([new_text]#, SUBSTITUTE([old_text]# + SUBSTITUTE(Client@row, " ", [old_text]#) + [old_text]#, [old_text]#, [new_text]#, 1)) - 1), "")
[w2] =IFERROR(MID(Client@row, FIND([new_text]#, SUBSTITUTE([old_text]# + SUBSTITUTE(Client@row, " ", [old_text]#) + [old_text]#, [old_text]#, [new_text]#, 2)), FIND([new_text]#, SUBSTITUTE([old_text]# + SUBSTITUTE(Client@row, " ", [old_text]#) + [old_text]#, [old_text]#, [new_text]#, 3)) - FIND([new_text]#, SUBSTITUTE([old_text]# + SUBSTITUTE(Client@row, " ", [old_text]#) + [old_text]#, [old_text]#, [new_text]#, 2)) - 1), "")

(The same patter for [w3] to [w5])

Then, use the following formula to get the Abbreviation.

=IF(

    OR(

        FIND(" ", Client@row) = 0,

        AND(

            FIND(" ", SUBSTITUTE(Client@row, " ", "", 1)) = 0,

            HAS({Short Words}, [w2]@row)

        )

    ),

    LEFT(Client@row, 7),

    IF(HAS({Short Words}, [w1]@row), "", LEFT([w1]@row, 1)) +

    IF(HAS({Short Words}, [w2]@row), "", LEFT([w2]@row, 1)) +

    IF(HAS({Short Words}, [w3]@row), "", LEFT([w3]@row, 1)) +

    IF(HAS({Short Words}, [w4]@row), "", LEFT([w4]@row, 1)) +

    IF(HAS({Short Words}, [w5]@row), "", LEFT([w5]@row, 1))

)

The {Short Words} range

https://app.smartsheet.com/b/publish?EQBCT=af0b8844258b4f78a17e7d122b489846

Explanation:

1. Single-Word Names or Single Significant Word:
   • The OR function checks two conditions:​
     • FIND(" ", Client@row) = 0: Determines if there's no space in the Client name, indicating a single-word name.
     • AND(FIND(" ", SUBSTITUTE(Client@row, " ", "", 1)) = 0, HAS({Short Words}, [w2]@row)): Checks if the name has only one significant word by removing the first space and verifying if the second word is a short word.
   • If either condition is true, LEFT(Client@row, 7) returns the first 7 characters of the Client name.​
2. Multi-Word Names:
   • For names with multiple words, the formula evaluates each word ([w1] through [w5]):​
     • IF(HAS({Short Words}, [w#]@row), "", LEFT([w#]@row, 1)): Checks if the word is in the {Short Words} list. If it is, it returns an empty string; otherwise, it returns the first letter of the word.
   • The results are concatenated to form the abbreviation.

Ayman Aboueid

@jmyzk_cloudsmart_jp, thank you! worked like a charm!

jmyzk_cloudsmart_jp

Happy to help!😁