Apr 27 2018
07:10 AM
- last edited on
Jul 25 2018
11:50 AM
by
TechCommunityAP
Apr 27 2018
07:10 AM
- last edited on
Jul 25 2018
11:50 AM
by
TechCommunityAP
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.
Apr 27 2018 08:27 AM - edited Apr 27 2018 08:31 AM
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
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
Apr 27 2018 08:54 AM
It has to be a formula. I can't use the Find and Replace dialog box. Thanks.
Apr 27 2018 09:28 AM
SolutionHi 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
Feb 14 2020 07:40 AM
Gorgeous solution thank you @Haytham Amairah
Sep 10 2020 07:04 AM
Oct 31 2021 12:11 PM
Oct 31 2021 12:13 PM
Apr 27 2018 09:28 AM
SolutionHi 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