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 36 characters. There can be multiple occurrences and what is between the two characters will vary from row to row. I cannot use VB only a formula.
Example:
Here is my text string:
Announcements|95029fcd-6b68-45bf-9f80-a2b2d90540f3;Personal Information|8096f02e-25e3-4416-8dbb-b2a58d309d4e;Personnel Statistical Reporting|9f1a73f0-5ce6-4abb-9fe1-44ab59350708;
What I need to do is remove from the string what is between "|" and ";" so my new text string looks like this:
Announcements, Personal Information, Personnel Statistical Reporting
With the formula below I get this. Only one instance is removed.
Announcements, Personal Information|8096f02e-25e3-4416-8dbb-b2a58d309d4e;Personnel Statistical Reporting|9f1a73f0-5ce6-4abb-9fe1-44ab59350708
=IFERROR(SUBSTITUTE(A1,MID(LEFT(A1,FIND(";",A1)),FIND("|",A1),LEN(A1)),", "),A1)
Thanks.
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
9 Replies
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 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.
- 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? Gorgeous solution thank you Haytham Amairah
