Pull text string between characters

  • Hello!

    I need to pull out some info from a text string with the following structure:


    I'd like to paste this text and have in the adjacent columns the text for "Market" and "Factory

    The pattern shows that factory will always be the last string and the market will be the string before last one. Meaning that the below can happen:



    So the order is from right to left.

    I've been able to pull out "Factory" by using this formula:

    =LEFT(RIGHT(A1,LEN(A1)-FIND("Ex ",A1)-2),4)

    However, as there are several "/" in the string, I don't know if there is a way to ask excel to seach the "second ocurrence of the symbol / from the right" or something like that to work with.

    Any ideas? Many thanks and the best weekend to you all!

  • I'm not sure what you are trying to do after you extract those words? Are you trying to extract other data to put into those columns?
    However, if you simply just want to extract only those words in adjacent cells then

    (formulas to go into adjacent cells)

  • Try:

    =TRIM(MID(SUBSTITUTE(A1,"/",REPT(" ",100)),1+(100*(LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))-2)),100))

    if you want to use similar formula to get the Factory:

    =TRIM(MID(SUBSTITUTE(A1,"/",REPT(" ",100)),1+(100*(LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))-1)),100))

    Where there is a will there are many ways. Finding one that works for you is the challenge!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!