Removing characters from cell - help req'd please

[Mag\(\)\(\)]

Hope someone can help me with this one....

I have text in column "A" a list of parts like the following.....

...nhg1234
..nhg1235.54
nhg3456
....nhg1253.7

Is thier anyway of removing the dots in front the part number?
I dont want the points moving after the the main part of the number.

Please help

TiA

mag()()

 

[Dave Peterson]

Do you part numbers all start with nhg?

If yes, you could select column A
edit|Replace
what: .n
with: n
replace all

And keep hitting the replace all button until all are fixed.

 

[Guest]

Mag
You could use this UDF - it will work with any number

Function delLeadDots(x)
l = Len(x) \ 2
For i = 1 To l
c = Mid(x, i, 1)
If c = "." Then
x = Application.WorksheetFunction.Substitute(x, ".", "", i)
i = i - 1
End If
Next
delLeadDots = x
End Function

Copy the function into a VB Module (ALT + F11, Insert, Module) and use like
any formula

Regards
Peter

 

[Mag\(\)\(\)]

The numbers begin with various letters and numbers.

I will have a with the module reply.

Thanks

 

[Dave Peterson]

If there are no spaces in your part number, you could use a formula:

=SUBSTITUTE(TRIM(SUBSTITUTE(A1,"."," "))," ",".")

 

[Mag\(\)\(\)]

Thanx Dave,
Worked a treat.

mag()()

If there are no spaces in your part number, you could use a formula:

=SUBSTITUTE(TRIM(SUBSTITUTE(A1,"."," "))," ",".")

 

[Guest]

Hi Richard

very good - I had a macro as well.

Sub delLeadDs()
Dim l As Integer, i As Integer, c
Dim tmp
For Each c In Selection
c.Select
l = Len(c) \ 2
tmp = c
For i = 1 To l
x = Mid(tmp, i, 1)
If x = "." Then
tmp = Application.WorksheetFunction.Substitute(tmp, ".", "", i)
i = i - 1
End If
Next i
c.Value = tmp
Next
End Sub

For interest maybe

Peter