Formula to only show characters before a specific character in a cell?

Mike Meyer · 09/21/21

Hey friends,

Needing some help creating a helper column. In column from imported report I get, "Asphalt Distributor (45)" where the "45" represents the quantity of assets in that category. I need to create a helper column that extracts the "(45)" and shows only the "Asphalt Distributor".

Please note: there are many asset categories, which means the number of digits before the (#) will vary, and of course the # of assets in each category will vary (#), (##), or (###).

Examples: Asphalt Distributor (45), Dump Truck (104), Material Transfer Machine (8)

Probably an easy formula, just not a function I've needed before.

Thank you very much!

Lidiya S. · 09/21/21

Hello @Mike Meyer !

You can do this with a combination of the LEFT and FIND functions. Pretty much you are asking "Where is the first "(" ? Now give me everything to the LEFT of it, minus 1 to account for the space."

Let me know how it goes!

=LEFT([Column21]@row, FIND("(", [Column21]@row) - 1)

=LEFT( Text you want to look at, FIND( where does the part you don't want start, 
       Text you want to look at) - # of characters)

Lidiya Shutaya

lidiya@ddbconstultants.ca

Andrew Stills · 09/21/21

I quick and easy solution would be to add leading zeros, I.e. (001) to the asset description and then use the REPLACE function to cut it out: https://help.smartsheet.com/function/replace

Substitute: https://help.smartsheet.com/function/substitute might also provide a more elegant solution.

Lidiya S. · 09/21/21

Hello @Mike Meyer !

You can do this with a combination of the LEFT and FIND functions. Pretty much you are asking "Where is the first "(" ? Now give me everything to the LEFT of it, minus 1 to account for the space."

Let me know how it goes!

=LEFT([Column21]@row, FIND("(", [Column21]@row) - 1)

=LEFT( Text you want to look at, FIND( where does the part you don't want start, 
       Text you want to look at) - # of characters)

Lidiya Shutaya

lidiya@ddbconstultants.ca

Mike Meyer · 09/21/21

@Lidiya S. Your solution worked great! Thank you very much! 😀

Lidiya S. · 09/21/21

@Mike Meyer Awesome! Happy to help!

Feel free to reach out if you have any more questions.

Lidiya Shutaya

lidiya@ddbconstultants.ca

Formula to only show characters before a specific character in a cell?

Best Answer

Answers

Help Article Resources

Categories