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

[Guest]

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

 

[Guest]

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

 

[Don Guillett]

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

 

[Roger Govier]

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))

 

[Bob Phillips]

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)

 

[Ragdyer]

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>.

 

[Don Guillett]

if?

 

[Ragdyer]

if?

.... Waiting for that second shoe to drop!<g>

 

[Dave Peterson]

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

 

[Ron Rosenfeld]

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

 

[se12]

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

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

 

[se12]

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

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]

 

[se12]

Thank you for your help!!!!

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

 

[se12]

Thank you for your help!!!!!

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

 

[se12]

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

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

 

[Bob Phillips]

All helps to keep the interest up <bg>

--
HTH

Bob Phillips

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

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

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

 

[RagDyeR]

They get deleted of course.

I really must take a reading comprehension course.<g>

Can't use old age excuse for everything.

 

[Dave Peterson]

Say it ain't so, Joe!

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

RagDyeR wrote:

 

[Gord Dibben]

Why not?

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


Gord

Can't use old age excuse for everything.

Gord Dibben MS Excel MVP