Text split

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.