Trim, Mid, substitute, len for the word following the space after a ~ character-CodePudding

I have various strings with varying length and word placement. The pattern is however, every time a word is found with "~" in front of it, I need to pull the word after the space following it. I've researched quite a bit on mid, left, right, etc functions, but have still not been able to come up with the result I need.

Here are 2 examples of strings:

TRANSACTION FEE: SOLD -1 1/1/2/2 ~IRON_CONDOR MA 100 18 MAR 22 385/390/305/300 CALL/PUT @2.37
TRANSACTION FEE: BOT 1 ~VERTICAL ANTM 100 (Weeklys) 4 MAR 22 480/485 CALL @.63

For number 1, "MA" should be the result. For number 2, "ANTM" should be the result.

Below are two formulas that seem to get me close to what I'm looking for, but I'm unable to connect the finished result because I just don't understand enough about them. My trials often result in errors haha erg.

=MID(A2,FIND("~",A2) 1,FIND(" ",A2,FIND(" ",A2) 1)-FIND(" ",A2)) '//This doesn't work because it returns "Iron_" for number 1 and "Verti" for number 2
=TRIM(MID(SUBSTITUTE(TRIM($A2)," ",REPT(" ",LEN($A2))), (7-1)*LEN($A2) 1, LEN($A2))) '//This doesn't work because the word needed isn't always the 7th word.
=TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",99)),MAX(1,FIND("~",SUBSTITUTE(A2," ",REPT(" ",99)))-50),99)) '//This returns the word that starts with the "~", but I need the word following it

I'm looking for an efficient formula that will 1st search for the word position that starts with the tilde "~" and then return the word following the space after that.

Anyone familiar with this that could offer a working solution?

CodePudding user response：

In B2, formula copied down :

=TRIM(MID(SUBSTITUTE(MID(A2,FIND("~",A2),99)," ",REPT(" ",99)),99,99))

And,

Your 3rd formula could be modified to this in obtain the target result :

=TRIM(MID(SUBSTITUTE($A2," ",REPT(" ",99)),MAX(1,FIND("~",SUBSTITUTE($A2," ",REPT(" ",99))) 99),99))

CodePudding user response：

Try this:

=LEFT(MID(RIGHT(A1,LEN(A1)-FIND("~",A1)),FIND(" ",RIGHT(A1,LEN(A1)-FIND("~",A1) 1)),LEN(A1)),FIND(" ",MID(RIGHT(A1,LEN(A1)-FIND("~",A1)),FIND(" ",RIGHT(A1,LEN(A1)-FIND("~",A1) 1)),LEN(A1))))