space in word
 

i want the space between word, i used the following VB Code, But its not work.

When i run the VB code, i get word seperated from number, but there is no
space between word.
what i have to make changes in VB Code.
I am really thankful to Gord Dibben MS Excel MVP.
again help expected

Sub RemoveNumbers()
' Remove alpha characters from a string.
' except for decimal points and hyphens.
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String

Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)

For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Mid(rngR.Value, intI, 1) Like "[a-z]" Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR

End Sub

space in word
 

Hi,

2 very small changes

Else: strNotNum = " "
Now has a space between the quotes

rngR.Value = WorksheetFunction.Trim(strTemp)
To get rid of leading spaces.

Sub RemoveNumbers()
' Remove alpha characters from a string.
' except for decimal points and hyphens.
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String

Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)

For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Mid(rngR.Value, intI, 1) Like "[a-z]" Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = " "
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = WorksheetFunction.Trim(strTemp)
Next rngR

End Sub

Mike

"hitesh" wrote:

i want the space between word, i used the following VB Code, But its not work.

When i run the VB code, i get word seperated from number, but there is no
space between word.
what i have to make changes in VB Code.
I am really thankful to Gord Dibben MS Excel MVP.
again help expected

Sub RemoveNumbers()
' Remove alpha characters from a string.
' except for decimal points and hyphens.
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String

Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)

For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Mid(rngR.Value, intI, 1) Like "[a-z]" Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR

End Sub