Formula to only show characters before a specific character in a cell?
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!
Best Answer
-
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
Answers
-
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.
Andrew
He who fails to plan is planning to fail. - Winston Churchill
-
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
-
@Lidiya S. Your solution worked great! Thank you very much! 😀
-
@Mike Meyer Awesome! Happy to help!
Feel free to reach out if you have any more questions.
Lidiya Shutaya
lidiya@ddbconstultants.ca
Help Article Resources
Categories
Check out the Formula Handbook template!