Fixing delimiters

[SlipEternal]

I have a formula that is grabbing data from lots of places and putting semicolons between the data. Sometimes, the data comes with semicolons already in place. Sometimes, the data is empty, and I don't want any semicolons.

What I wind up with is a string that sometimes starts with one or more semicolons. When this happens, I want to remove the starting semicolons (I want to start the string at the first non-semicolon character).
Sometimes, the string ends with a semicolon. When this happens, I want to end the string with the last non-semicolon value.
Sometimes, the string will contain multiple semicolons between two substrings (example: Bob;;Dole). When this happens, I want to replace the middle semicolons with a single semicolon.

Much of the spreadsheet is locked, so I would like to do this in a single cell, if possible.

Let's say the current formula is
=FORMULA

I was thinking of something like this to trim the initial and ending semicolons:
=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(FORMULA," ",CHAR(1)),";"," "))," ",";"),CHAR(1)," ")

This takes care of all of the initial and ending semicolons, but not the duplicates in the middle. Is there a simple formula to take care of them?

This gets me close:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(FORMULA," ",CHAR(1)),";"," "))," ",";"),CHAR(1)," "),";;",";")

But, if there are more than 2 consecutive semicolons, like Bob;;;Dole, it only reduces the number of semicolons by 1.

[Glenn Kennedy]

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[Glenn Kennedy]

What's the maximum number of consecutive semicolons?

Edit: This seems to work as desired....


=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(LEFT(A1,1)=";",MID(IF(RIGHT(A1,1)=";",LEFT(A1,LEN(A1)-1),A1),2,LEN(A1)-2),IF(RIGHT(A1,1)=";",LEFT(A1,LEN(A1)-1),A1)),";;;;",";"),";;;",";"),";;",";")

[SlipEternal]

Attached is a sample with exactly 6 possible semicolons getting added. This means that getting rid of leading or terminating semicolons, I only need to worry about at most 4 "inner" semicolons. So, I made it so that it can handle up to 4 "inner" semicolons. But, as soon as I increase the number of mantles it can handle, it adds the possibility of a 5th internal semicolon that my code will not handle.

[Glenn Kennedy]

I didn't see any ;; in your "sample"

[SlipEternal]

I didn't see any ;; in your "sample"

Correct. I gave my solution to the problem that works for up to four consecutive ;'s. I can probably figure it out. I was just hoping someone had a formula for turning multiple repeated characters into a single character where there was no limit to the number of repeated characters.

[CK76]

Can't you replace all instance of ";" with single space. Then use trim and substitute back ";"?

=SUBSTITUTE(TRIM(SUBSTITUTE(A2,";"," "))," ",";")

Edit: Oh, wait, you got spaces in your original string. You'll need additional step. Replace initial space character with some non-existent text such as "@".
So...
=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(A2," ","@"),";"," "))," ",";"),"@"," ")

[SlipEternal]

Can't you replace all instance of ";" with single space. Then use trim and substitute back ";"?

=SUBSTITUTE(TRIM(SUBSTITUTE(A2,";"," "))," ",";")

Edit: Oh, wait, you got spaces in your original string. You'll need additional step. Replace initial space character with some non-existent text such as "@".
So...
=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(A2," ","@"),";"," "))," ",";"),"@"," ")

Yes, my original post has almost this exact formula. I used CHAR(1) rather than "@". This gets rid of leading and trailing semicolons. The issue is now getting rid of the duplicate middle semicolons. So, I was hoping for a formula that would take any number of middle semicolons and turn it into just one semicolon. Using repeated SUBSTITUTE functions, I can make a specific number of repeated semicolons go to one semicolon, but not an arbitrarily large number.

[CK76]

Hmm? The formula should work for however many number of semicolons you have repeated.

1. It first replaces existing " " single space with "@" (or any non-existent character).
2. Then replaces ALL instance of ";" with " " single space.
3. Use trim to get rid of preceding and trailing space, and keep only single space in between.
4. Replace single space with ";".
5. Replace "@" back to single space.

See attached.

[SlipEternal]

Hmm? The formula should work for however many number of semicolons you have repeated.

1. It first replaces existing " " single space with "@" (or any non-existent character).
2. Then replaces ALL instance of ";" with " " single space.
3. Use trim to get rid of preceding and trailing space, and keep only single space in between.
4. Replace single space with ";".
5. Replace "@" back to single space.

See attached.

Oh, awesome. I was just working through the logic. I had not realized that my formula actually worked! You are correct. It takes care of all of the extra semicolons.

Thanks!

[CK76]

You are welcome and thanks for the rep