"Replace With" option

[Joey]

Hi all

I would like to amend a column with phone numbers and change only th
first two characters to a "0"

Most of the numbers look like this;
44131 555 8000

It should all read like this;
0131 555 8000

I could use the Ctl H feature but it would affect the number if "44
appeared in the middle or end of the number.


Is this possible??


Thanks for any advice
Joe

 

[AlfD]

Hi!

Try this in another column and copy down:

=IF(LEFT(C2,2)="44","0"&MID(C2,3,LEN(C2)-2),C2)

where your number is in C2.

If you have large number of unwanted prefixes, you might find it usefu
to analyse them with Data > AutoFilter, if only to get a list of them.

But you can then ask AutoFilter to do a Custom selection using thing
like "Begins with".

Al

 

[Don Guillett]

try
Sub makezeros()
For Each c In Selection
c.Value = "00" & Right(c, Len(c) - 2)
Next c
End Sub