Forum Discussion

MrsSandra's avatar
MrsSandra
Occasional Reader
Jul 10, 2026

Text split

I have this informatie in a cel:

WIJNVEEN 15693 | 88/38 | 31-12-2024 |  [WIJNVEEN|15693]

I need the two components at the end, the parts between [ and ]

So one cel is "WIJNVEEN" and the second cel is "15693".

Can anyone help with 1 formule for each cel, so no "in between" cels?

 

excel
Formulas and Functions

2 Replies

  • m_tarler's avatar
    m_tarler
    Silver Contributor
    Jul 10, 2026

    You can do both cells with 1 formula:

    =TEXTSPLIT(TEXTBEFORE(TEXTAFTER(A1,"["),"]"),"|")

    Or split into 2 formula:

    =TEXTBEFORE(TEXTAFTER(A1,"["),"|")
=TAKE(TEXTSPLIT(TEXTBEFORE(A1,"]"),"|"),,-1)

    Or you can use RegEx but I'll leave that to someone better with RegEx than me.

    I did assume there was only and always 1 case of [ ] but didn't assume it was always at the very end of the cell.  You may also want to add the IfNotFound optional parameters if that is reasonably possible.

    If you want to use an input array like A1:A100 then you will need to tweak it or use LAMBDA to make them work

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Jul 10, 2026

    Your full text is in cell A1:

    WIJNVEEN 15693 | 88/38 | 31-12-2024 | [WIJNVEEN|15693]

    You want two separate cells – one with WIJNVEEN and one with 15693.

     

    Option 1: Modern Excel (Microsoft 365 / Excel 2021 or later)

    These functions are the simplest and most readable.

    First value – the text between [ and |

    =TEXTBEFORE(TEXTAFTER(A1;"[");"|")

    Second value – the text between | and ]

    =TEXTAFTER(TEXTBEFORE(A1;"]");"|")

    If your Excel uses commas instead of semicolons, replace ; with ,.

    How it works:

    • The first formula takes everything after the [, then keeps only what is before the first |.
    • The second formula first cuts the text before the ] (so it ends just before the bracket), then takes everything after the last | in that remaining part.

     

    Option 2: Classic Excel (all versions, including older ones)

    These use MID and FIND – they work everywhere.

    First value

    =MID(A1; FIND("[";A1)+1; FIND("|";A1; FIND("[";A1)) - FIND("[";A1) -1)

    Second value

    =MID(A1; FIND("|";A1; FIND("[";A1))+1; FIND("]";A1) - FIND("|";A1; FIND("[";A1)) -1)

    Again, switch ; to , if needed.

    How it works (simple explanation):

    • FIND("[",A1) locates the opening bracket.
    • FIND("|",A1, FIND("[",A1)) finds the pipe after that opening bracket.
    • The MID function then takes a substring starting right after the [ (or after the pipe for the second value) with the correct number of characters (the position difference minus one).
    • The second formula does the same, but starts after the pipe inside the brackets and ends just before the ].

     

    If you dealing with unexpected spaces…

    If your data might have extra spaces, wrap any formula in TRIM() to clean the result, e.g.:

    =TRIM(TEXTBEFORE(TEXTAFTER(A1;"[");"|"))

    Both approaches give you exactly WIJNVEEN and 15693 without needing any helper cells.

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

     

    Was the answer useful? Mark as best response and like it!

    This will help all forum participants.