Remove duplicate characters from a text string in a cell

I have an excel spread-sheet with following data. I need help in reducing the ";" down to 1. if there is only 1 semi colon then leave it as is.
It appears simple but i am just not able to solve it.
Any help will be greatly appreciated.

2120;;;;;;;;;;;;
;;;;idea;22045;;;;;;;
;;;;xxxxx;00000;;;;;;;detail


Thanks in advance.

Hi sam99,

Do a FIND and REPLACE (ctrl-H)...

Find what ;;
Replace with ;

Replace All until all duplicates are gone...

re-post: highlight your range just in case you don't want others to change

Thanks. It Work. Can we do the same thing using a formula?

I tried using a formula, but you'd have to have to use the formula several times...
=SUBSTITUTE(C1,";;",";")
then copy this several times across to all to itself...

It was not very elegant, that's why I simply tried the find and replace...

Almost, tigeravatar, from my understanding, sam99 wants to keep 1 semicolons where there are repeated semicolons... then again, I can be mistaken...

That's it... awesome formula, tigeravatar!

Great job, tiger.

The only thing I can add to this is this: Why are you (Sam99) ending up with strings in cells with lots of semi-colons? I am assuming you must be using a formula or VBA to concatenate a bunch of other data. If that is the case, then you might consider altering your original code/formula that produces those strings so that it only adds a semi-colon if it finds data to concatenate. That way, you don't need to worry about a formula that removes the extras.

- Moo

First of all Thanks to djapigo and tigeravatar for helping me out with a formula. The first formula that tigeravatar provided is what i was looking for.

Moo - The reason why we end up with a lot of semicolon is concatenating 22 cell. I do not know vba. writing a formula to concatenate and trim blanks for 22 cells appeared messy to me.

Sorry about getting back so late.
Thanks
Sam

Here is a function that you could use to concatenate a range of cells (vertically or horizontally) with semi-colons that will produce clean results (i.e. only semi-colons between found values within the range):

Please Login or Register  to view this content.

You can place that in a VBA Module by doing this:
1. Open your workbook and press Alt+F11
2. Select Insert >> Module
3. Paste the above code into that module
4. Save your worksheet as a Macro-Enabled Worksheet (.xlsm)
5. Close out the Excel VBA window

Now, in that workbook, whenever you need to concatenate a range of cells using a semi-colon as a delimiter, use the function 'ConcatRange' like this:

Formula:

Please Login or Register  to view this content.

or

Formula:

Please Login or Register  to view this content.

It skips blank cells, so if you have a range of 22 cells that you need to concatenate, it will only return the cells with values in them, separated by semi-colons.

Hope this helps!

- Moo

Amazing Stuff.. Thanks a lot Moo.

Moo - Eight years later - THANK YOU! I was already up to a couple hours and eight helper columns trying to noodle through a workaround because I couldn't find any combination of formulas to do what I needed. This ConcatRange function is brilliant. Thank you for sharing.