Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete space after word | Excel Discussion (Misc queries) | |||
Can I automatically add space after ea. row ("Space After" in Word | Excel Discussion (Misc queries) | |||
When added to the end of one word the space bar changes the word | Excel Discussion (Misc queries) | |||
Word: replace ... ... with space | Excel Discussion (Misc queries) | |||
How do I clear blank space at the top of a word-wrapped text cell? | Excel Discussion (Misc queries) |