Change first character in a cell
 

I have a list of +2000 phonenumbers and they are in the format of
012345678 but must become 3212345678, so the first 0 needs to become 32

I searched, but was unable to find a solution. Any idea?

houghi
--
Theologians can pursuade themselves of anything. Anyone who can worship
a trinity and insists that his religion is a monotheism can believe
anything -- just give him time to rationalize it.
Robert A. Heinlein, JOB: A Comedy of Justice

Change first character in a cell
 

In a "helper" column put:

="32"& RIGHT(A1,LEN(A1)-1)

Copy down.

Copy & Paste Special=Values to remove" formula.

"houghi" wrote:

I have a list of +2000 phonenumbers and they are in the format of
012345678 but must become 3212345678, so the first 0 needs to become 32

I searched, but was unable to find a solution. Any idea?

houghi
--
Theologians can pursuade themselves of anything. Anyone who can worship
a trinity and insists that his religion is a monotheism can believe
anything -- just give him time to rationalize it.
Robert A. Heinlein, JOB: A Comedy of Justice

Change first character in a cell
 

A macro perhaps:-

Sub changefirst()
Dim rcell As Range
Dim Myrange As Range
Dim maxval As Variant
Set Myrange = Range("A1:A100") '<Change to suit
newprefix = "32"
For Each rcell In Myrange
newvalue = newprefix & Mid(rcell.Value, 2, 99)
rcell.Value = newvalue
Next
End Sub

Mike

"houghi" wrote:

I have a list of +2000 phonenumbers and they are in the format of
012345678 but must become 3212345678, so the first 0 needs to become 32

I searched, but was unable to find a solution. Any idea?

houghi
--
Theologians can pursuade themselves of anything. Anyone who can worship
a trinity and insists that his religion is a monotheism can believe
anything -- just give him time to rationalize it.
Robert A. Heinlein, JOB: A Comedy of Justice

Change first character in a cell
 

Apparently it contains an error.

Toppers wrote:
In a "helper" column put:

="32"& RIGHT(A1,LEN(A1)-1)

Copy down.

Copy & Paste Special=Values to remove" formula.

"houghi" wrote:

I have a list of +2000 phonenumbers and they are in the format of
012345678 but must become 3212345678, so the first 0 needs to become 32

I searched, but was unable to find a solution. Any idea?


houghi
--
Theologians can pursuade themselves of anything. Anyone who can worship
a trinity and insists that his religion is a monotheism can believe
anything -- just give him time to rationalize it.
Robert A. Heinlein, JOB: A Comedy of Justice

Change first character in a cell
 

This works well enough, thanks.

Mike H wrote:
A macro perhaps:-

Sub changefirst()
Dim rcell As Range
Dim Myrange As Range
Dim maxval As Variant
Set Myrange = Range("A1:A100") '<Change to suit
newprefix = "32"
For Each rcell In Myrange
newvalue = newprefix & Mid(rcell.Value, 2, 99)
rcell.Value = newvalue
Next
End Sub

Mike

"houghi" wrote:

I have a list of +2000 phonenumbers and they are in the format of
012345678 but must become 3212345678, so the first 0 needs to become 32

I searched, but was unable to find a solution. Any idea?

houghi
--
Theologians can pursuade themselves of anything. Anyone who can worship
a trinity and insists that his religion is a monotheism can believe
anything -- just give him time to rationalize it.
Robert A. Heinlein, JOB: A Comedy of Justice



houghi
--
Theologians can pursuade themselves of anything. Anyone who can worship
a trinity and insists that his religion is a monotheism can believe
anything -- just give him time to rationalize it.
Robert A. Heinlein, JOB: A Comedy of Justice

Change first character in a cell
 

Nothing wrong with Toppers' formula.

Apparently you typed or copied incorrectly.

But I see from other post you got the job done.


Gord Dibben MS Excel MVP


On Thu, 24 May 2007 17:04:05 +0200, houghi wrote:

Apparently it contains an error.

Toppers wrote:
In a "helper" column put:

="32"& RIGHT(A1,LEN(A1)-1)

Copy down.

Copy & Paste Special=Values to remove" formula.

"houghi" wrote:

I have a list of +2000 phonenumbers and they are in the format of
012345678 but must become 3212345678, so the first 0 needs to become 32

I searched, but was unable to find a solution. Any idea?


houghi