Forum Discussion

Chris Langham's avatar
Chris Langham
Copper Contributor
Apr 27, 2018
Solved

Remove text between two characters multiple times

I have a column that has a text string with various lengths. What I'm trying to do is remove text that starts with "|" and ends with ";". The text between those two characters is always going to be 3...
Formulas & Functions
Need Help
  • Haytham Amairah's avatar
    Haytham Amairah
    Apr 27, 2018

    Hi Chris,

     

    Please try this very long formula:

    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),MID(LEFT(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),SEARCH(";",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),SEARCH("|",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", ")),LEN(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),", "),MID(LEFT(SUBSTITUTE(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),MID(LEFT(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),SEARCH(";",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),SEARCH("|",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", ")),LEN(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),", "),SEARCH(";",SUBSTITUTE(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),MID(LEFT(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),SEARCH(";",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),SEARCH("|",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", ")),LEN(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),", "))),SEARCH("|",SUBSTITUTE(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),MID(LEFT(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),SEARCH(";",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),SEARCH("|",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", ")),LEN(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),", ")),LEN(SUBSTITUTE(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),MID(LEFT(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),SEARCH(";",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),SEARCH("|",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", ")),LEN(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),", "))),", "),", ","",(LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),MID(LEFT(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),SEARCH(";",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),SEARCH("|",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", ")),LEN(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),", "),MID(LEFT(SUBSTITUTE(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),MID(LEFT(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),SEARCH(";",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),SEARCH("|",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", ")),LEN(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),", "),SEARCH(";",SUBSTITUTE(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),MID(LEFT(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),SEARCH(";",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),SEARCH("|",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", ")),LEN(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),", "))),SEARCH("|",SUBSTITUTE(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),MID(LEFT(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),SEARCH(";",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),SEARCH("|",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", ")),LEN(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),", ")),LEN(SUBSTITUTE(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),MID(LEFT(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),SEARCH(";",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),SEARCH("|",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", ")),LEN(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),", "))),", "))-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),MID(LEFT(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),SEARCH(";",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),SEARCH("|",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", ")),LEN(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),", "),MID(LEFT(SUBSTITUTE(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),MID(LEFT(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),SEARCH(";",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),SEARCH("|",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", ")),LEN(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),", "),SEARCH(";",SUBSTITUTE(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),MID(LEFT(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),SEARCH(";",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),SEARCH("|",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", ")),LEN(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),", "))),SEARCH("|",SUBSTITUTE(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),MID(LEFT(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),SEARCH(";",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),SEARCH("|",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", ")),LEN(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),", ")),LEN(SUBSTITUTE(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),MID(LEFT(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "),SEARCH(";",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),SEARCH("|",SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", ")),LEN(SUBSTITUTE(A1,MID(LEFT(A1,SEARCH(";",A1)),SEARCH("|",A1),LEN(A1)),", "))),", "))),", "),", ","")))/2)

     

    And find it in the attached file.

     

    Regards

Haytham Amairah's avatar
Haytham Amairah
Silver Contributor
Apr 27, 2018

Hi Chris,

 

I can give you an easy and fairly good solution away from the VBA, which can complete the task by 99%.

 

This is done by using the Find and Replace tool.

 

Select all the targeted cells

  •     Press Ctrl+H to open the Find and Replace tool
  •     In Find what box, type:  |*;
  •     In Replace with box, type:  , (Comma and space), and then press Replace All

 

This result will show as follows:

Announcements, Personal Information, Personnel Statistical Reporting,

 

But you can use this formula to remove the last comma and space from the string.

=SUBSTITUTE(A1,", ","",(LEN(A1)-LEN(SUBSTITUTE(A1,", ","")))/2)

 

Hope that helps

Haytham

SquiggleArt's avatar
SquiggleArt
Copper Contributor
Jun 10, 2025

I literally created an account, simply to come on, 7 years later, hoping the OP sees this.

That is one of the most beautiful excel formulas I have ever seen.

I thought I've come up with some impressive 12 deep nested ifs and vlookups... 

I stand back in awe of a master.

Resources