Macro to delete a specific sign

  • Thread starter Thread starter LEG
  • Start date Start date
L

LEG

Hi!
I have two systems which deliver the samme ID-number in two different formats.
1. 080808-0000 with the format Tekst
2. 0808080000 with the format "number saved as tekst"

no. 2 is easy enough in the 2007 version to change to a numberformat.

In the case of no. 1 can someone help to find a macro that would
automatically delete the '-', and change the format to numbers. As I have a
list of 2000 ID-no. it would take some time to delete the '-' manuelly. Any
help would be appreciated.
 
Select the cells you want to change and run:

Sub FixFormat()
Dim r As Range, s As String
s = "-"
For Each r In Selection
v = r.Text
If InStr(v, s) <> 0 Then
v = Replace(v, s, "")
r.Clear
r.NumberFormat = "0000000000"
r.Value = v
End If
Next
End Sub
 
Back
Top