| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I change the character separating thousands or decimals on. | Excel Discussion (Misc queries) | |||
| How to change character to lower case except the first letter? | Excel Discussion (Misc queries) | |||
| How can I change 255 character limit in 'hyperlink' function? | Excel Worksheet Functions | |||
| Change character while copy | Excel Worksheet Functions | |||
| Copy a column in worksheet with a character change | Excel Worksheet Functions | |||
Linear Mode
