Replacing 2+ consecutive characters

**PennyKat** · 12-13-2011, 05:49 PM

Hi and thanks in advance!

I'm hoping this will be simple and I'm just overlooking something obvious. I have a cell containing the following text...**the string of hyphens are representing multiple spaces for this example. My preview indicated the multiple spaces would not show up once posted**

------ALL CAPS TOGETHER----- all lower case together--- finally this one---------

This above is all in one cell - we'll say A2. My sheet contains several hundred cells that mimic this pattern...the cells start with a string of spaces (again, represented by the dash marks), then there are phrases separated by undetermined amount of space groups in the middle (varying number of phrases and spaces cell to cell), and end with yet even more spaces.

Using just the example above, what I want to end up with for this cell is:

ALL CAPS TOGETHER, all lower case together, finally this one

So I'll have replaced anywhere there are two or more consecutive spaces with ", " and removed the beginning and end space altogether. Can anyone help? THANK YOU

**aldek** · 12-13-2011, 06:33 PM

Hi,
If they are all in one column, you can do this:
Do "Text to columns" in the data tab, seperate all of the information into 3 seperate cells, then in cell d2 put =concatanate(a2,b2,c2).
I haven't tried, so save before you try it yourself.
Good luck.

**dip11** · 12-13-2011, 07:22 PM

Make a separate worksheet.
In there, type in A1:
=TRIM(Sheet1!A1)

Then fill rows and columns with it.

This will remove all excess spaces from start, middle and end of cells. It would give you:
ALL CAPS TOGETHER all lower case together finally this one

Without the commas unfortunately.

Then save and export as a txt csv. Import that into excel using the import wizard and specify commas as deliminators.

**PennyKat** · 12-14-2011, 11:48 AM

Thanks aldek, but unfortunately I have an undetermined number of phrases per cell. Some of them are in the hundreds so manually typing them in is what I want to avoid. The example I gave was just that, an example

dip11, thanks that's more in the right direction. Unfortunately the same as above applies. For me to comma delimit all phrases would be extremely daunting.

I appreciate the help though!

**dip11** · 12-14-2011, 12:13 PM

Comma delimiting would be automatic, like a tickbox. You wouldn't have to select every single phrase.

**PennyKat** · 12-14-2011, 12:44 PM

My problem I think is that It's separating each word when I use delimited instead of only separating phrases. Each row has a different number of phrases at different lengths.. I also can't use the fixed width when importing. Also, you mentioned above to specify commas as the delimiter, but the saved txt csv you reference doesn't have commas.

**PennyKat** · 12-14-2011, 06:04 PM

I've figured out what I needed to do. This is the code that ended up working great for me and my needs on this project...

Function ConcatRange(rCat As Range) As String
    Dim cell        As Range
    Dim rInt        As Range

    Set rInt = Intersect(rCat.Worksheet.UsedRange, rCat)
    If Not rInt Is Nothing Then
        For Each cell In rInt
            If Len(cell.Text) Then
                ConcatRange = ConcatRange & cell.Text & " "
            Else
                If Right(ConcatRange, 1) <> "/" Then ConcatRange = ConcatRange & "/"
            End If
        Next cell
    End If
    
    If Left(ConcatRange, 1) = "/" Then ConcatRange = Mid(ConcatRange, 2)
End Function