Removing characters from cell - help req'd please

M

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()()
 
D

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.
 
B

Billy Liddel

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
 
M

Mag\(\)\(\)

The numbers begin with various letters and numbers.

I will have a with the module reply.

Thanks
 
D

Dave Peterson

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

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

Mag\(\)\(\)

Thanx Dave,
Worked a treat.

mag()()


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

=SUBSTITUTE(TRIM(SUBSTITUTE(A1,"."," "))," ",".")
Click to expand...
 
B

Billy Liddel

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
 
Top