Extra space in cells

[mamen]

Here is my problem: I need to split cells but I need to do it with some kind of conditional formatting.

Right now these cells have "(" "," " " so the only thing that is different is that in between what i want to split there are at least 2 spaces blank or more.

What i thought about is to have a formula that goes like: If blank space is > 1 then replace with "{". Then after that's done, it will be easy to split text to columns using "{". I Hope i make any sense.

Remember the data in all these cells is different with numbers, text different length and the only thing that makes a difference is space in between the characters is > 1.

 

[Gary McMaster]

Try the sample below on a test copy of your sheet. It should leave no more than two spaces anywhere. It is operating on column B. You'll have to change that to suit.

Gary

Public Sub Kill_Spaces()

Dim oRange As Range
Dim oFound As Range

'Change "B:B" to correct column or range
Set oRange = ActiveSheet.Range("B:B")

Do

    Set oFound = oRange.Find(what:="   ") '3 spaces
    
    If Not oFound Is Nothing Then
        'Replace 3 spaces with 2 spaces
        oFound.Replace what:="   ", Replacement:="  "
    End If

Loop Until oFound Is Nothing

MsgBox "Done"

End Sub

 

[bkaehny]

The location of the double-space marks the point at which the original poster wants to split the cell, so you can't get rid of the double space. In addition, it's sometimes many spaces...though now that I think of it, you can just replace all double spaces with the delimiter symbol and then when you do the text-to-columns you need to select the option that says "treat consecutive delimiters as one." That should solve the problem.

 

[Gary McMaster]

though now that I think of it, you can just replace all double spaces with the delimiter symbol and then when you do the text-to-columns you need to select the option that says "treat consecutive delimiters as one." That should solve the problem.

Very nice! Didn't know that option existed.

Gary

 

[pgc01]

you can just replace all double spaces with the delimiter symbol and then when you do the text-to-columns you need to select the option that says "treat consecutive delimiters as one." That should solve the problem.

Hi

That's a nice solution.

You'll be left, however, with some heading spaces when you have a sequence of an odd number of spaces (>1).

An alternative, that would get rid of those extra spaces using the replace tool would be:

- replace each 2 spaces with a "|"
- replace "| " with "|"
- now use the Text-to-Columns (with the "treat consecutive delimiters as one") as bkaehny suggested

This seems to be a quick solution, although you could also try a solution with a formula, an udf or some code that would perform the splitting on a column.

 

[mamen]

So even though I thought I tried replace 2 spaces for { and then click the option on split "treat same as one", I thought it didn't work which puzzled me. I just tried again and it worked! Thanks everyone for you help!!!!