Extract the text to the left of the rightmost character in the column

Jesse Shockley

I need to extract the text to the left of a hyphen in a column, but some entries have two hyphens (actually it's a hyphen plus a space, but I'm sure I can figure that part out.) so it has to be the text to the left of the rightmost hyphen in the cell. I poked around the help forum then asked the AI formula generator. It game me the following formula, but it produces an #INVALID DATA error.

=LEFT([VFM Category Main]@row, FIND("-", [VFM Category Main]@row, LEN([VFM Category Main]@row) - FIND("-", SUBSTITUTE([VFM Category Main]@row, "-", "", LEN([VFM Category Main]@row) - LEN(SUBSTITUTE([VFM Category Main]@row, "-", ""))))) - 1)

Jesse Shockley

I came up with the answer. Using Paul Newcome's article on extracting text to the right of the rightmost character in a cell (https://community.smartsheet.com/discussion/90980/isolating-all-text-to-the-right-of-a-character-e-g-in-a-string) I altered the formula to come up with this:

=LEFT([VFM Category Main]@row, (FIND(CHAR(10), SUBSTITUTE([VFM Category Main]@row, "-", CHAR(10), LEN([VFM Category Main]@row) - LEN(SUBSTITUTE([VFM Category Main]@row, "-", ""))))) - 1)

EDIT - I guess I can't mark my own response as answered. @Lisa LS Kennedy would make sense for you to repost this so I can mark it as answered?

Lisa LS Kennedy

Hi Jesse,

The formula appears to be valid and I can confirm that it works in my sheet. If you are getting an Invalid Data Type error it's because the reference in the formula is incompatible with that data column that it's in or that it's referencing. What types of columns are you using?

Lisa Kennedy

Senior Consultant | Smartsheet Development

Prime Consulting Group

Email: info@primeconsulting.com

Jesse Shockley

Sorry, the error message is #INVALID VALUE

The data in the referenced cell (anonymized) is:

School - Private - Bxxx Txxx Catholic School

Lisa LS Kennedy

Try this out. Ironically, the spaces do mater here b/c the Len is counting those spaces and the invalid value is because the lengths don't "match" when we take out the extra space and substitute it.

=LEFT([VFM Category Main]@row, FIND(" -", [VFM Category Main]@row, FIND(" -", [VFM Category Main]@row) + 1) - 1

Lisa Kennedy

Senior Consultant | Smartsheet Development

Prime Consulting Group

Email: info@primeconsulting.com

Jesse Shockley

That works for that cell. However, it does not seem to work for the cell below it, which only contains one hyphen.

Florist Shop - Blxxxxxx Exxxxx

Jesse Shockley

I came up with the answer. Using Paul Newcome's article on extracting text to the right of the rightmost character in a cell (https://community.smartsheet.com/discussion/90980/isolating-all-text-to-the-right-of-a-character-e-g-in-a-string) I altered the formula to come up with this:

=LEFT([VFM Category Main]@row, (FIND(CHAR(10), SUBSTITUTE([VFM Category Main]@row, "-", CHAR(10), LEN([VFM Category Main]@row) - LEN(SUBSTITUTE([VFM Category Main]@row, "-", ""))))) - 1)

EDIT - I guess I can't mark my own response as answered. @Lisa LS Kennedy would make sense for you to repost this so I can mark it as answered?