Forum Discussion
Extract a portion of text
Hi.
For the life of me, I can't work out the formula to extract a portion of text before and after a text delimiter.
Column A - First part of text in cell only - SOLVED
Column B - Raw Data
Column C - Require the second part of the text only. - UNSOLVED
For example from row 2
A2 B2 C2
IA20240429001294 Raw Data Bateau Bay
Any help would be very appreciated before i loose all of my hair!!
Simon_Fish To extract the text between two delimiters you need to combine TEXTBEFORE and TEXTAFTER.
Try this in column C:
=TEXTBEFORE(TEXTAFTER(B5," - ")," - ")
5 Replies
- Riny_van_EekelenPlatinum Contributor
Not sure I understand how the COUNTIF formula can return any other value than FALSE. In your example, the first formula returns 001 in case "Entity^" exists and an #N/A error if it doesn't. Then, I would wrap the formula in IFERROR like this:
=IFERROR(TEXTBEFORE(TEXTAFTER(AH14,"Entity^"),"^"),"")
- Riny_van_EekelenPlatinum Contributor
Simon_Fish To extract the text between two delimiters you need to combine TEXTBEFORE and TEXTAFTER.
Try this in column C:
=TEXTBEFORE(TEXTAFTER(B5," - ")," - ")
- KWalkerCopper Contributor
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?- EObergCopper Contributor
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!
- Simon_FishCopper Contributor