Dear All,

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

Thanks

Thanh Nguye

--
thanhnguye
-----------------------------------------------------------------------
thanhnguyen's Profile: http://www.excelforum.com/member.php...fo&userid=3050
View this thread: http://www.excelforum.com/showthread.php?threadid=55828

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

NickHK

"thanhnguyen"
wrote in message
...

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

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"
wrote in message
...

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

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

Did you read the replies you have received ?

NickHK

"thanhnguyen"
wrote in message
...

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