Extract number various length from middle of string

[meierli]

I would like to extract the number between TRN*1* and the next *. Currently i have a formula that is based off the same characters (*) but recently my data set changed so that I need to bring in additional information as my first value in the formula (TRN*1*).

Any suggestions on how to update the formula?

Example

REMARK=TRN*1*0823572801*1954402957\,ORIG BANK

Current formula

MID(T6027,FIND("*",T6027,FIND("*",T6027)+1)+1,FIND("*",T6027,FIND("*",T6027,FIND("*",T6027)+1)+1)-FIND("*",T6027,FIND("*",T6027)+1)-1

[#2]

@meierli Let's say the text string is in A1.

If you are on an older Excel version this formula should work:

=LEFT(RIGHT(A1,LEN(A1)-(FIND("TRN*1*",A1)+5)),FIND("*",RIGHT(A1,LEN(A1)-(FIND("TRN*1*",A1)+5)))-1)

If you are on Excel 2021, you can reduce duplication of arguments using the LET function.

If, on the other hand you are on Excel for MS365 use this:

=TEXTBEFORE(TEXTAFTER(A1,"TRN*1*"),"*")

[#3]

@Riny_van_Eekelen 

Thank you the 1st formula worked like a charm.

I discovered now a 2nd instance that I would need to bring into the formula. Th text string as the first argument can be either TRN*1* or TRN*1**.

My attempt was to try an iferror formula, but it just returns a blank cell.

=IFERROR(LEFT(RIGHT(T335,LEN(T335)-(FIND("TRN*1*",T335)+5)),FIND("*",RIGHT(T335,LEN(T335)-(FIND("TRN*1*",T335)+5)))-1), LEFT(RIGHT(T335,LEN(T335)-(FIND("TRN*1**",T335)+6)),FIND("*",RIGHT(T335,LEN(T335)-(FIND("TRN*1**",T335)+6)))-1))

Btw, I am on Office 365 but the 2nd option with textbefore/after available for whatever reason.

[#4 · Accepted solution]

@meierli Well, this is becoming increasingly ugly :)

You could use SUBSTITUTE to replace two consecutive asterisks by one. Just replace A1 by SUBSTITUTE(A1,"**","*") for every occasion. But the repetition isn't very elegant, even though it works.

=LEFT(RIGHT(SUBSTITUTE(A1,"**","*"),LEN(SUBSTITUTE(A1,"**","*"))-(FIND("TRN*1*",SUBSTITUTE(A1,"**","*"))+5)),FIND("*",RIGHT(SUBSTITUTE(A1,"**","*"),LEN(SUBSTITUTE(A1,"**","*"))-(FIND("TRN*1*",SUBSTITUTE(A1,"**","*"))+5)))-1)

If you have a large data set and other inconsistencies turn up, perhaps better to look into Power Query and deal with them there. Much cleaner than working with regular formulas and trying to capture them with nested IF statements.

[#5]

Thank you. I just hope that with the next upgrade of the companies office 365 I will have textbefore/after available. For now that will do :)