Forum Discussion
Extract number various length from middle of string
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.
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.
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.
- 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 🙂