One for the Excel gurus

VoucherMan

I have some files I work on which have text strings with all the spaces removed.

LikeThisForExample.

I found a function which adds a space between the words

Function InsSpace(txt As String) As String
With CreateObject("VBScript.RegExp")
.Pattern = "(\B[A-Z])"
.Global = True
InsSpace = .Replace(txt, " " & "$1")
End With
End Function


Unfortunately it only adds a space before capitals. Occasionally I'll get numbers ( Christmas2013 ) or an & symbol ( Black&White ) where it doesn't work and I'm having to edit manually.

Can anyone suggest a modification that would separate the additional characters, on a standalone function?

I'm sure there's a solution out there but so far Google's let me down. I'm using Excel 2007 in case it makes a difference.

DevCoder

The \B is matching any nonword boundary

\d is used for digits so "(\B[A-Z]+\d{1,})" should also check for numbers (the {1,) means match at least 1 numeric)

You have to escape out any special characters individually

so \&\?\$\.\(\)\[\]\/\\ would be an example of some of the special characters.

I haven't got a dev machine to hand so cant test the above.

andydiysaver

Personally I'd do it via ASCII- this is demo code I've done for you, but you need to modify it for your purposes with the special characters. If you want to know an ASCII code of a character - e.g. CAPS A is 65, just put = CODE (A2) and put your letter in A2


This demo code can be modified and it already weeds out numbers etc. Trick I used is to use a variable string F which spaces a number then sets itself to empty. hope you find useful- should give you some ideas


Sub Demo()
Dim txt As String
txt = ActiveCell.Value
Dim newstring As String
newstring = Left(txt, 1)
MsgBox (Len(txt))
Dim F As String
F = " "
For I = 2 To Len(txt)




Select Case Asc(Mid(txt, I, 1))



Case Is > 64
If Asc(Mid(txt, I, 1)) > 91 Then GoTo Out
If Asc(Mid(txt, I, 1)) < 91 Then newstring = newstring + " " + Mid(txt, I, 1)
Case Is > 47

If Asc(Mid(txt, I, 1)) > 58 Then GoTo Out
If Asc(Mid(txt, I, 1)) < 58 Then newstring = newstring + F + Mid(txt, I, 1)
If Asc(Mid(txt, I, 1)) < 58 Then F = "" Else F = " "
Case Is > 32
If Asc(Mid(txt, I, 1)) > 47 Then GoTo Out
If Asc(Mid(txt, I, 1)) < 47 Then newstring = newstring + F + Mid(txt, I, 1)
If Asc(Mid(txt, I, 1)) < 47 Then F = "" Else F = " "

Case Else
Out:
newstring = newstring + Mid(txt, I, 1)

End Select
MsgBox (newstring)
Next I
ActiveCell.Value = newstring
End Sub

DevCoder

Not bad code Andy but regular expression matching is optimal for search / replace. The op could take either route but depending on the amount of rows to check regexp would become far more efficient the byte checking of a string.

VoucherMan

Thanks for the suggestions. Unfortunately I couldn't get the first one to work & Andy's suggestion looked a bit too long for my liking.

Not to be beaten though I registered on the Excel forum where I found the original function and after asking the question there was soon offered

Function InsSpace(txt As String) As String
With CreateObject("VBScript.RegExp")
.Pattern = "(\B[A-Z])"
.Global = True
txt = .Replace(txt, " " & "$1")
.Pattern = "((\D)(?=\d)|(\d)(?=\D))"
.Global = True
txt = .Replace(txt, "$1 ")
InsSpace = Replace(txt, "&", " & ")
End With
End Function

which works fine.