is there a formula that i can drag or macro that allows me to get rid of
specific digits?

Example data:

20
1191
88018
1514
119411
119147
11551123
119146

I need to get rid of all "11" that are in the beginning, however, not if
they are at the end or in the middle.

So the data would look after editing like:

20
91
88018
1514
9411
9147
551123
9146

This is a multi-step process. First, enter this formula in a cell on same
row with your data (presumes the 20 is in A1, and this formula is in B1)

=IF(LEFT(A1,2)="11",RIGHT(A1,LEN(A1)-2),A1)
fill it down the sheet as far as your numbers go, it will remove "11" from
the beginning of any that have it, and leave the others alone. But it has
the effect of converting any numbers changed into text that looks like
numbers.

To fix that, enter a 1 into any empty cell in the worksheet, then select
that cell and choose Edit | Copy
Then select the range of cells where you have the formulas, and use
Edit | Paste Special | [Multiply]
this will put them all into number format again, but the formula is still in
there, so if you do away with anything in A, you'll get an error.

If you want to now convert the formulas to values, select all the cells with
formulas in them (again if you unselected them from before) then Edit | Copy
and then
Edit | Paste Special | [Values]
and the formulas are gone, and you have real numbers in their place.


"A.S." wrote:

is there a formula that i can drag or macro that allows me to get rid of
specific digits?

Example data:

20
1191
88018
1514
119411
119147
11551123
119146

I need to get rid of all "11" that are in the beginning, however, not if
they are at the end or in the middle.

So the data would look after editing like:

20
91
88018
1514
9411
9147
551123
9146

Looks like i only needed to do the formula, and it worked. Thanks!!!!

"JLatham" wrote:

This is a multi-step process. First, enter this formula in a cell on same
row with your data (presumes the 20 is in A1, and this formula is in B1)

=IF(LEFT(A1,2)="11",RIGHT(A1,LEN(A1)-2),A1)
fill it down the sheet as far as your numbers go, it will remove "11" from
the beginning of any that have it, and leave the others alone. But it has
the effect of converting any numbers changed into text that looks like
numbers.

To fix that, enter a 1 into any empty cell in the worksheet, then select
that cell and choose Edit | Copy
Then select the range of cells where you have the formulas, and use
Edit | Paste Special | [Multiply]
this will put them all into number format again, but the formula is still in
there, so if you do away with anything in A, you'll get an error.

If you want to now convert the formulas to values, select all the cells with
formulas in them (again if you unselected them from before) then Edit | Copy
and then
Edit | Paste Special | [Values]
and the formulas are gone, and you have real numbers in their place.


"A.S." wrote:

is there a formula that i can drag or macro that allows me to get rid of
specific digits?

Example data:

20
1191
88018
1514
119411
119147
11551123
119146

I need to get rid of all "11" that are in the beginning, however, not if
they are at the end or in the middle.

So the data would look after editing like:

20
91
88018
1514
9411
9147
551123
9146