"Replace With" option

Hi all

I would like to amend a column with phone numbers and change only the
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
Joey


---
Message posted from http://www.ExcelForum.com/

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 useful
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 things
like "Begins with".

Alf


---
Message posted from http://www.ExcelForum.com/

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

--
Don Guillett
SalesAid Software

"Joey " wrote in message
...
Hi all

I would like to amend a column with phone numbers and change only the
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
Joey


---
Message posted from http://www.ExcelForum.com/