excelstringfindandmodify

Excel: is there a way to replace a peculiar part of a cell string?

I have an excel export with a column containing staff previous training sessions in a string. Example: First Name|Last Name|Training title|January 2018 to February 2018

Due to non obligatory form fields from which data was collected, many strings have no end month value, like this January 2018 to I'm trying to find a formula to find all strings ending with to (hence followed by nothing else) and delete this orphaned part of the string so it will look like January 2018.

Find and replace is useless as it also replaces the value when there's an end month/year. Substitute seems like a good way to go but I've failed trying to find the proper structure to the formula.

Solution

  • Test whether it ends in to with right, and if so then use left to omit it:

    =IF(RIGHT(A1,2) = "to",LEFT(A1,LEN(A1)-2),A1)