Remove space and "-" character

[thanhnguyen]

Dear All,

Could you help me to remove space and "-" in cell's value?
Ex : 123 456 - 789 = 123456789

Thanks

Thanh Nguyen

[NickHK]

One way:
Range("A1").Text=Replace(Range("A1").Text, " - ","")
combined with .Find or loop through cells.

NickHK

"thanhnguyen" <[email protected]>
wrote in message
news:[email protected]...
>
> Dear All,
>
> Could you help me to remove space and "-" in cell's value?
> Ex : 123 456 - 789 = 123456789
>
> Thanks
>
> Thanh Nguyen
>
>
> --
> thanhnguyen
> ------------------------------------------------------------------------
> thanhnguyen's Profile:
http://www.excelforum.com/member.php...o&userid=30502
> View this thread: http://www.excelforum.com/showthread...hreadid=558287
>

[Tim Marsh]

if you want it to apply to the whole sheet, its probably just as easy to use
Edit > Replace... from the menu bar, eg, find "-" (without quotes) and
replace with nothing, then do teh same for the "space"



"thanhnguyen" <[email protected]>
wrote in message
news:[email protected]...
>
> Dear All,
>
> Could you help me to remove space and "-" in cell's value?
> Ex : 123 456 - 789 = 123456789
>
> Thanks
>
> Thanh Nguyen
>
>
> --
> thanhnguyen
> ------------------------------------------------------------------------
> thanhnguyen's Profile:
> http://www.excelforum.com/member.php...o&userid=30502
> View this thread: http://www.excelforum.com/showthread...hreadid=558287
>

[thanhnguyen]

Hello,

I found this function on MSDN

Function ReplaceWord(strText As String, _
strFind As String, _
strReplace As String) As String

' This function searches a string for a word and replaces it.
' You can use a wildcard mask to specify the search string.

Dim astrText() As String
Dim lngCount As Long

' Split the string at specified delimiter.
astrText = Split(strText)

' Loop through array, performing comparison
' against wildcard mask.
For lngCount = LBound(astrText) To UBound(astrText)
If astrText(lngCount) Like strFind Then
' If array element satisfies wildcard search,
' replace it.
astrText(lngCount) = strReplace
End If
Next
' Join string, using same delimiter.
ReplaceWord = Join(astrText)
End Function

This function can replace "-" character but space can not. I also found the function TrimSpace which remove extra spaces from a string:

Function TrimSpace(strInput As String) As String
' This procedure trims extra space from any part of
' a string.

Dim astrInput() As String
Dim astrText() As String
Dim strElement As String
Dim lngCount As Long
Dim lngIncr As Long

' Split passed-in string.
astrInput = Split(strInput)

' Resize second array to be same size.
ReDim astrText(UBound(astrInput))

' Initialize counter variable for second array.
lngIncr = LBound(astrInput)
' Loop through split array, looking for
' non-zero-length strings.
For lngCount = LBound(astrInput) To UBound(astrInput)
strElement = astrInput(lngCount)
If Len(strElement) > 0 Then
' Store in second array.
astrText(lngIncr) = strElement
lngIncr = lngIncr + 1
End If
Next
' Resize new array.
ReDim Preserve astrText(LBound(astrText) To lngIncr - 1)

' Join new array to return string.
TrimSpace = Join(astrText)
End Function


but still not solve my problem, please give me any advice.
Thanks

Thanh Nguyen

[NickHK]

Did you read the replies you have received ?

NickHK

"thanhnguyen" <[email protected]>
wrote in message
news:[email protected]...
>
> Hello,
>
> I found this function on MSDN
>
> Function ReplaceWord(strText As String, _
> strFind As String, _
> strReplace As String) As String
>
> ' This function searches a string for a word and replaces it.
> ' You can use a wildcard mask to specify the search string.
>
> Dim astrText() As String
> Dim lngCount As Long
>
> ' Split the string at specified delimiter.
> astrText = Split(strText)
>
> ' Loop through array, performing comparison
> ' against wildcard mask.
> For lngCount = LBound(astrText) To UBound(astrText)
> If astrText(lngCount) Like strFind Then
> ' If array element satisfies wildcard search,
> ' replace it.
> astrText(lngCount) = strReplace
> End If
> Next
> ' Join string, using same delimiter.
> ReplaceWord = Join(astrText)
> End Function
>
> This function can replace "-" character but space can not. I also found
> the function TrimSpace which remove extra spaces from a string:
>
> Function TrimSpace(strInput As String) As String
> ' This procedure trims extra space from any part of
> ' a string.
>
> Dim astrInput() As String
> Dim astrText() As String
> Dim strElement As String
> Dim lngCount As Long
> Dim lngIncr As Long
>
> ' Split passed-in string.
> astrInput = Split(strInput)
>
> ' Resize second array to be same size.
> ReDim astrText(UBound(astrInput))
>
> ' Initialize counter variable for second array.
> lngIncr = LBound(astrInput)
> ' Loop through split array, looking for
> ' non-zero-length strings.
> For lngCount = LBound(astrInput) To UBound(astrInput)
> strElement = astrInput(lngCount)
> If Len(strElement) > 0 Then
> ' Store in second array.
> astrText(lngIncr) = strElement
> lngIncr = lngIncr + 1
> End If
> Next
> ' Resize new array.
> ReDim Preserve astrText(LBound(astrText) To lngIncr - 1)
>
> ' Join new array to return string.
> TrimSpace = Join(astrText)
> End Function
>
>
> but still not solve my problem, please give me any advice.
> Thanks
>
> Thanh Nguyen
>
>
> --
> thanhnguyen
> ------------------------------------------------------------------------
> thanhnguyen's Profile:
http://www.excelforum.com/member.php...o&userid=30502
> View this thread: http://www.excelforum.com/showthread...hreadid=558287
>