Remove Superfluous Spaces

Re: Remove Spaces Inside Words

Just use the TRIM function like [frc]=TRIM(A1)[/frc] then Copy and Paste Special as Values.

Re: Remove Superfluous Spaces

TRIM won't affect the text given in the example.

Are all the words in initial capital format? If so, a formula could be made to correct it, I think. If not, there is no way to tell where each word begins.

Re: Remove Superfluous Spaces

As BTC2 says, you will only be able to do this if every word begins with a capital letter. The following script removes these spaces in the given sample file (you need to select the cells before you run it):

Sub Remove_Spaces()
Dim rng As Range
With CreateObject("VBScript.Regexp")
    .Global = True
    .Pattern = "([A-Za-z]) +([a-z])"
    For Each rng In Selection
        rng.Value = .Replace(rng.Value, "$1$2")
    Next rng
End With
End Sub

Hope it helps!

Richard

Re: Remove Superfluous Spaces

Ok, all you need to do is this;

Select one of the cells.
From the Formula bar copy (Ctrl+C) one of these unseen characters (which aren't spaces).
Go Edit>Replace and paste in the copied character (Ctrl+V) and leave the Replace with: blank.
Click Replace All.

Re: Remove Superfluous Spaces

try

Sub test()
Dim r As Range, rng As Range
Set rng = ActiveSheet.UsedRange.SpecialCell(2,3)
With CreateObject("VBScript.RegExp")
     .Pattern = "[\f\n\r\t\v]+"
     .Global = True
     For Each r in .Cells
          r.Value = .replace(r.Value, "")
     Next
End With
End Sub