Forum Discussion
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...
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
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
I have a similar question.
I need to replace e.g. the following in Word: <em>people</em> where the word ‘people’ must be changed to ‘people’ in italics. The word ‘people’ can be any word or words between between <em> and </em>.
How do I do that please?
I need to replace e.g. the following in Word: <em>people</em> where the word ‘people’ must be changed to ‘people’ in italics. The word ‘people’ can be any word or words between between <em> and </em>.
How do I do that please?