Delete the number "49" from each cell in a column
 

In a column of numbers, SOME cell of numbers START WITH "23", I need to
delete the first two numbers (23) only and leave the rest of the numbers in
that cell as they appear. A Column can have up to 600 cells. Also, the
numbers are Imported into Excel as TEXT Format. Can you help me...Thank You!!

Example: 231234567890 (Numbers Imported into Excel as TEXT Format)
Need To Look Like This: 1234567890

Delete the number "49" from each cell in a column
 

in a "helper" column

=IF(LEFT(A1,2)="23",RIGHT(A1,LEN(A1)-2),A1)

Copy down

Copy/past special=values in "helper" column or original

HTH

"se12" wrote:

In a column of numbers, SOME cell of numbers START WITH "23", I need to
delete the first two numbers (23) only and leave the rest of the numbers in
that cell as they appear. A Column can have up to 600 cells. Also, the
numbers are Imported into Excel as TEXT Format. Can you help me...Thank You!!

Example: 231234567890 (Numbers Imported into Excel as TEXT Format)
Need To Look Like This: 1234567890

Delete the number "49" from each cell in a column
 

Or a macro something like
for each c in range("a2:a"&cells(rows.count,"a").end(xlup).row))
c.value=IF(LEFT(c,2)="23" then RIGHT(c,LEN(c)-2))
next

--
Don Guillett
SalesAid Software

"se12" wrote in message
...
In a column of numbers, SOME cell of numbers START WITH "23", I need to
delete the first two numbers (23) only and leave the rest of the numbers
in
that cell as they appear. A Column can have up to 600 cells. Also, the
numbers are Imported into Excel as TEXT Format. Can you help me...Thank
You!!

Example: 231234567890 (Numbers Imported into Excel as TEXT Format)
Need To Look Like This: 1234567890

Delete the number "49" from each cell in a column
 

Hi

Try
=IF(LEFT(A1,2)="23",MID(A1,3,10),A1)
If you want these values to display as numbers then precede the whole
formula with the double unary minus, which will coerce the result from
Text to Numeric

=--(IF(LEFT(A1,2)="23",MID(A1,3,10),A1))

--
Regards

Roger Govier


"se12" wrote in message
...
In a column of numbers, SOME cell of numbers START WITH "23", I need
to
delete the first two numbers (23) only and leave the rest of the
numbers in
that cell as they appear. A Column can have up to 600 cells. Also,
the
numbers are Imported into Excel as TEXT Format. Can you help
me...Thank You!!

Example: 231234567890 (Numbers Imported into Excel as TEXT Format)
Need To Look Like This: 1234567890

Delete the number "49" from each cell in a column
 

I love the way the subject says delete 49, and the text says delete 23 ?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Don Guillett" wrote in message
...
Or a macro something like
for each c in range("a2:a"&cells(rows.count,"a").end(xlup).row))
c.value=IF(LEFT(c,2)="23" then RIGHT(c,LEN(c)-2))
next

--
Don Guillett
SalesAid Software

"se12" wrote in message
...
In a column of numbers, SOME cell of numbers START WITH "23", I need to
delete the first two numbers (23) only and leave the rest of the numbers
in
that cell as they appear. A Column can have up to 600 cells. Also, the
numbers are Imported into Excel as TEXT Format. Can you help me...Thank
You!!

Example: 231234567890 (Numbers Imported into Excel as TEXT Format)
Need To Look Like This: 1234567890

Delete the number "49" from each cell in a column
 

Select the column, then,
<Data <Text To Columns

Click on "Fixed Width", then <Next,

Click in the "Preview Window" and place the 'break line' to separate the
first 2 numbers from the rest.
Then <Next

This first column (2 numbers) is selected by default.
Click on "Do Not Import"
The column header changes to "Skip"

NOW, some choices!

Click in the second column to select it.
The header says "General" (for format type).
You can change that to text if you wish by clicking on "Text", or just leave
it as General

You now have the choice of *changing (deleting)* the original column and
replacing it with this new configuration (eliminating the first 2 numbers),
OR
Preserving the original column and insert this newly configured column
elsewhere.

To *replace* the old with the new, click <Finish

To *retain* the old, click in the "Destination" box, and change the default
location (original column location), to some other vacant column of your
choice, *then* click <Finish.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"se12" wrote in message
...
In a column of numbers, SOME cell of numbers START WITH "23", I need to
delete the first two numbers (23) only and leave the rest of the numbers
in
that cell as they appear. A Column can have up to 600 cells. Also, the
numbers are Imported into Excel as TEXT Format. Can you help me...Thank
You!!

Example: 231234567890 (Numbers Imported into Excel as TEXT Format)
Need To Look Like This: 1234567890

Delete the number "49" from each cell in a column
 

if?

--
Don Guillett
SalesAid Software

"Ragdyer" wrote in message
...
Select the column, then,
<Data <Text To Columns

Click on "Fixed Width", then <Next,

Click in the "Preview Window" and place the 'break line' to separate the
first 2 numbers from the rest.
Then <Next

This first column (2 numbers) is selected by default.
Click on "Do Not Import"
The column header changes to "Skip"

NOW, some choices!

Click in the second column to select it.
The header says "General" (for format type).
You can change that to text if you wish by clicking on "Text", or just
leave
it as General

You now have the choice of *changing (deleting)* the original column and
replacing it with this new configuration (eliminating the first 2
numbers),
OR
Preserving the original column and insert this newly configured column
elsewhere.

To *replace* the old with the new, click <Finish

To *retain* the old, click in the "Destination" box, and change the
default
location (original column location), to some other vacant column of your
choice, *then* click <Finish.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"se12" wrote in message
...
In a column of numbers, SOME cell of numbers START WITH "23", I need to
delete the first two numbers (23) only and leave the rest of the numbers
in
that cell as they appear. A Column can have up to 600 cells. Also, the
numbers are Imported into Excel as TEXT Format. Can you help me...Thank
You!!

Example: 231234567890 (Numbers Imported into Excel as TEXT Format)
Need To Look Like This: 1234567890

Delete the number "49" from each cell in a column
 

if?

.... Waiting for that second shoe to drop!<g
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Don Guillett" wrote in message
...
if?

--
Don Guillett
SalesAid Software

"Ragdyer" wrote in message
...
Select the column, then,
<Data <Text To Columns

Click on "Fixed Width", then <Next,

Click in the "Preview Window" and place the 'break line' to separate the
first 2 numbers from the rest.
Then <Next

This first column (2 numbers) is selected by default.
Click on "Do Not Import"
The column header changes to "Skip"

NOW, some choices!

Click in the second column to select it.
The header says "General" (for format type).
You can change that to text if you wish by clicking on "Text", or just
leave
it as General

You now have the choice of *changing (deleting)* the original column and
replacing it with this new configuration (eliminating the first 2
numbers),
OR
Preserving the original column and insert this newly configured column
elsewhere.

To *replace* the old with the new, click <Finish

To *retain* the old, click in the "Destination" box, and change the
default
location (original column location), to some other vacant column of your
choice, *then* click <Finish.

--
HTH,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-

"se12" wrote in message
...
In a column of numbers, SOME cell of numbers START WITH "23", I need to
delete the first two numbers (23) only and leave the rest of the
numbers
in
that cell as they appear. A Column can have up to 600 cells. Also,
the
numbers are Imported into Excel as TEXT Format. Can you help
me...Thank
You!!

Example: 231234567890 (Numbers Imported into Excel as TEXT Format)
Need To Look Like This: 1234567890

Delete the number "49" from each cell in a column
 

What happens to the leading two characters IF the value don't start with 23 (or
49)?



Ragdyer wrote:

if?

... Waiting for that second shoe to drop!<g
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Don Guillett" wrote in message
...
if?

--
Don Guillett
SalesAid Software

"Ragdyer" wrote in message
...
Select the column, then,
<Data <Text To Columns

Click on "Fixed Width", then <Next,

Click in the "Preview Window" and place the 'break line' to separate the
first 2 numbers from the rest.
Then <Next

This first column (2 numbers) is selected by default.
Click on "Do Not Import"
The column header changes to "Skip"

NOW, some choices!

Click in the second column to select it.
The header says "General" (for format type).
You can change that to text if you wish by clicking on "Text", or just
leave
it as General

You now have the choice of *changing (deleting)* the original column and
replacing it with this new configuration (eliminating the first 2
numbers),
OR
Preserving the original column and insert this newly configured column
elsewhere.

To *replace* the old with the new, click <Finish

To *retain* the old, click in the "Destination" box, and change the
default
location (original column location), to some other vacant column of your
choice, *then* click <Finish.

--
HTH,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-

"se12" wrote in message
...
In a column of numbers, SOME cell of numbers START WITH "23", I need to
delete the first two numbers (23) only and leave the rest of the
numbers
in
that cell as they appear. A Column can have up to 600 cells. Also,
the
numbers are Imported into Excel as TEXT Format. Can you help
me...Thank
You!!

Example: 231234567890 (Numbers Imported into Excel as TEXT Format)
Need To Look Like This: 1234567890




--

Dave Peterson

Delete the number "49" from each cell in a column
 

On Mon, 4 Sep 2006 10:49:01 -0700, se12 wrote:

In a column of numbers, SOME cell of numbers START WITH "23", I need to
delete the first two numbers (23) only and leave the rest of the numbers in
that cell as they appear. A Column can have up to 600 cells. Also, the
numbers are Imported into Excel as TEXT Format. Can you help me...Thank You!!

Example: 231234567890 (Numbers Imported into Excel as TEXT Format)
Need To Look Like This: 1234567890

And another method.

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

Then use the formula:

=REGEX.SUBSTITUTE(A1,"^23")

If you want to remove either 23 or 49 if they are the first two characters,
then use:

=REGEX.SUBSTITUTE(A1,"^(23|49)")


--ron

Delete the number "49" from each cell in a column
 

Toppers I used your solution and IT WORKED LIKE A CHARM.....THANK YOU SO MUCH!
!!!

Toppers wrote:
in a "helper" column

=IF(LEFT(A1,2)="23",RIGHT(A1,LEN(A1)-2),A1)

Copy down

Copy/past special=values in "helper" column or original

HTH

In a column of numbers, SOME cell of numbers START WITH "23", I need to
delete the first two numbers (23) only and leave the rest of the numbers in
[quoted text clipped - 3 lines]
Example: 231234567890 (Numbers Imported into Excel as TEXT Format)
Need To Look Like This: 1234567890

Delete the number "49" from each cell in a column
 

Sorry for the confusion, forgot to change the 49....LOL...

Bob Phillips wrote:
I love the way the subject says delete 49, and the text says delete 23 ?

Or a macro something like
for each c in range("a2:a"&cells(rows.count,"a").end(xlup).row))
[quoted text clipped - 10 lines]
Example: 231234567890 (Numbers Imported into Excel as TEXT Format)
Need To Look Like This: 1234567890

--
Thank you for your help!!!! Have a GREAT DAY!!!!

Delete the number "49" from each cell in a column
 

Thank you for your help!!!!

Don Guillett wrote:
Or a macro something like
for each c in range("a2:a"&cells(rows.count,"a").end(xlup).row))
c.value=IF(LEFT(c,2)="23" then RIGHT(c,LEN(c)-2))
next

In a column of numbers, SOME cell of numbers START WITH "23", I need to
delete the first two numbers (23) only and leave the rest of the numbers
[quoted text clipped - 5 lines]
Example: 231234567890 (Numbers Imported into Excel as TEXT Format)
Need To Look Like This: 1234567890

Delete the number "49" from each cell in a column
 

Thank you for your help!!!!!

Roger Govier wrote:
Hi

Try
=IF(LEFT(A1,2)="23",MID(A1,3,10),A1)
If you want these values to display as numbers then precede the whole
formula with the double unary minus, which will coerce the result from
Text to Numeric

=--(IF(LEFT(A1,2)="23",MID(A1,3,10),A1))

In a column of numbers, SOME cell of numbers START WITH "23", I need
to
[quoted text clipped - 7 lines]
Example: 231234567890 (Numbers Imported into Excel as TEXT Format)
Need To Look Like This: 1234567890

--
Thank you for your help!!!! Have a GREAT DAY!!!!

Delete the number "49" from each cell in a column
 

Thank you for the software suggestion, I will try it.....

Ron Rosenfeld wrote:
In a column of numbers, SOME cell of numbers START WITH "23", I need to
delete the first two numbers (23) only and leave the rest of the numbers in
[quoted text clipped - 3 lines]
Example: 231234567890 (Numbers Imported into Excel as TEXT Format)
Need To Look Like This: 1234567890

And another method.

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

Then use the formula:

=REGEX.SUBSTITUTE(A1,"^23")

If you want to remove either 23 or 49 if they are the first two characters,
then use:

=REGEX.SUBSTITUTE(A1,"^(23|49)")

--ron

--
Thank you for your help!!!! Have a GREAT DAY!!!!

Delete the number "49" from each cell in a column
 

All helps to keep the interest up <bg

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"se12" <u26173@uwe wrote in message news:65d2e90a73046@uwe...
Sorry for the confusion, forgot to change the 49....LOL...

Bob Phillips wrote:
I love the way the subject says delete 49, and the text says delete 23 ?

Or a macro something like
for each c in range("a2:a"&cells(rows.count,"a").end(xlup).row))
[quoted text clipped - 10 lines]
Example: 231234567890 (Numbers Imported into Excel as TEXT Format)
Need To Look Like This: 1234567890

--
Thank you for your help!!!! Have a GREAT DAY!!!!

Delete the number "49" from each cell in a column
 

They get deleted of course.

I really must take a reading comprehension course.<g

Can't use old age excuse for everything.
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

"Dave Peterson" wrote in message
...
What happens to the leading two characters IF the value don't start with 23
(or
49)?



Ragdyer wrote:

if?

... Waiting for that second shoe to drop!<g
--
Regards,

RD

--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit !
--------------------------------------------------------------------------
-
"Don Guillett" wrote in message
...
if?

--
Don Guillett
SalesAid Software

"Ragdyer" wrote in message
...
Select the column, then,
<Data <Text To Columns

Click on "Fixed Width", then <Next,

Click in the "Preview Window" and place the 'break line' to separate
the
first 2 numbers from the rest.
Then <Next

This first column (2 numbers) is selected by default.
Click on "Do Not Import"
The column header changes to "Skip"

NOW, some choices!

Click in the second column to select it.
The header says "General" (for format type).
You can change that to text if you wish by clicking on "Text", or just
leave
it as General

You now have the choice of *changing (deleting)* the original column
and
replacing it with this new configuration (eliminating the first 2
numbers),
OR
Preserving the original column and insert this newly configured column
elsewhere.

To *replace* the old with the new, click <Finish

To *retain* the old, click in the "Destination" box, and change the
default
location (original column location), to some other vacant column of
your
choice, *then* click <Finish.

--
HTH,

RD



--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may
benefit
!


--------------------------------------------------------------------------
-

"se12" wrote in message
...
In a column of numbers, SOME cell of numbers START WITH "23", I need
to
delete the first two numbers (23) only and leave the rest of the
numbers
in
that cell as they appear. A Column can have up to 600 cells. Also,
the
numbers are Imported into Excel as TEXT Format. Can you help
me...Thank
You!!

Example: 231234567890 (Numbers Imported into Excel as TEXT Format)
Need To Look Like This: 1234567890




--

Dave Peterson

Delete the number "49" from each cell in a column
 

Say it ain't so, Joe!

I plan to, er, I use that excuse for everything now!

RagDyeR wrote:
<<snipped

Can't use old age excuse for everything.
--

Delete the number "49" from each cell in a column
 

Why not?

Putting in all those years must have some sort of accrued benefit<g


Gord

On Tue, 5 Sep 2006 09:10:54 -0700, "RagDyeR" wrote:

Can't use old age excuse for everything.

Gord Dibben MS Excel MVP