Search code examples
excelstringreturnformula

Excel: Returning characters to the left and right of the last (or second to last) occurrence?

I have a string like this: 100 Stupid Street ; Government Tower 7; Chicago Palm, MA 92111

I want to return all the characters to the left of the last semicolon.

To the left of the last semicolon would be: 100 Stupid Street ; Government Tower 7

That was step 1. I would also like to extract characters from this string: Chicago Palm, MA 92111

I want to return all the characters to the right and left of the second to last space.

So, To the right of the second to last space would be: MA 92111

To the left of the second to last space would be: Chicago Palm,

If there is a way to remove that last comma in that last formula, that would be even better.

This formula has helped me a bit:

=TRIM(RIGHT(SUBSTITUTE(A1,";",REPT(" ",LEN(A1))),LEN(A1)))

Cell A1 has the original string. This formula successfully returns everything to the right of the last semicolon.

Solution

  • If you are looking to parse out all of the pieces, some of the previous returns can assist in the next operation(s) so portions of formulas do not have to be repeated.

        stupid street

    B1 is =LEFT(SUBSTITUTE(A3, ";", "×",LEN(A3)-LEN(SUBSTITUTE(A3,";",""))), FIND("×",SUBSTITUTE(A3&"×", ";", "×",LEN(A3)-LEN(SUBSTITUTE(A3,";",""))))-1)

    B2 is =TRIM(RIGHT(SUBSTITUTE(A2,";",REPT(" ",99)),99))

    B3 is =TRIM(RIGHT(SUBSTITUTE(A3,",",REPT(" ",99)),99))

    B4 is =TRIM(SUBSTITUTE(SUBSTITUTE(A4,B3,""),",",""))