Forum Discussion

Simon_Fish's avatar
Simon_Fish
Copper Contributor
May 10, 2024
Solved

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!!

excel
Formulas and Functions
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    May 10, 2024

    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

Sort By
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Apr 08, 2025

    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_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    May 10, 2024

    Simon_Fish To extract the text between two delimiters you need to combine TEXTBEFORE and TEXTAFTER.

    Try this in column C:

    =TEXTBEFORE(TEXTAFTER(B5," - ")," - ")

     

    • KWalker's avatar
      KWalker
      Copper Contributor
      Apr 07, 2025

      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?

      • EOberg's avatar
        EOberg
        Copper Contributor
        Apr 16, 2025

        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!

Resources