Forum Discussion
Extract a portion of text
Simon_Fish To extract the text between two delimiters you need to combine TEXTBEFORE and TEXTAFTER.
Try this in column C:
=TEXTBEFORE(TEXTAFTER(B5," - ")," - ")
You are the only person I've found that has given any advice on what I'm trying to do, so if you have any advice:
I have a list of data that looks like this:
Entity^001^Program^12510^Organization^023022^Account^550000^Location^0000^Project^000000
But depending on what the user has opted into, they may not have all the pieces.
I'm trying to identify if they have the piece (e.g. Entity) to provide the value after the ^.
So the formula =TEXTBEFORE(TEXTAFTER(AH14,"Entity^"),"^") works and outputs 001, but that assumes that I always have an entity value. So I'm now trying to build in an if function.
And it works to show be if I have it =IF(COUNTIF(AH15,"Entity"),"Yes",""), and leaves it blank if I don't, but I'm trying to combine them into:
=IF(COUNTIF(AH14,"Entity"),TEXTBEFORE(TEXTAFTER(AH14,"Entity^"),"^"),"")
And it's not working (it's leaving everything blank, even if it's got the value)
Any advice?
I assume the data will always contain the same number of markers. For example, if the user does not input the Organization value, that portion of the list will be "...Organization^^Account..."
If this is the case, I would use the optional instance number parameter in TEXTBEFORE and TEXTAFTER to capture the data between each set of markers. Any missing data will result in an empty cell.
For example, say your list of data is in A1 and we put the extraction formulas in A2:C7. The formulas in A2, B2, and C7 will be unique, but the others can just be copied to the rows below (indicated by ...).
Here are the formulas:
A2: =1
A3: =A2+2
...
A7: =A6+2
B2: =TEXTBEFORE($A$1,"^",A2)
B3: =TEXTAFTER(TEXTBEFORE($A$1,"^",A3),"^",A3-1)
...
B7: =TEXTAFTER(TEXTBEFORE($A$1,"^",A7),"^",A7-1)
C2: =TEXTAFTER(TEXTBEFORE($A$1,"^",A2+1),"^",A2)
...
C6: =TEXTAFTER(TEXTBEFORE($A$1,"^",A6+1),"^",A6)
C7: =TEXTAFTER($A$1,"^",A7)
I hope this helps!