Removing unwanted digits

[Guest]

Hi,

i'm not sure how to explain this but here goes...

i have cells containing 12 digit numbers. I need to remove the first 3 and
the last 2 digits. As there are 1000 plus cells (all in the same colum) which
i need this to occur is there a way to automate this inexcel?

thanks,

Bill

 

[David Biddulph]

=MID(A1,4,7) and copy down.

 

[Guest]

thanks for the response david.

let me explain a little further..

For example... currently I have the following in cell A1... H016690180

I wish to remove the H0 from the beginning and the 180 from the end....

Is this possible to have excel perform this automatically?

thanks again,

Bill

 

[Guest]

Perhaps Text-to-Columns?

If all cell values are 12 characters in length
try something like this:

Select the single-column range of cells

Then...from the Excel main menu:
<data><text-to-columns>
Check: Fixed width..........click [Next]
Click between the 2nd and 3rd characters (to insert a break point)
Click between the 9th and 10th characters (to insert another break point)
Click [Next]
Click on the 1st of the 3 columns....Check: Do not import column
Click on the 3rd of the 3 columns....Check: Do not import column
Click [Finish]

That will lop off the 1st 2 and the last 3 characters

(BTW....Your example of H016690180 only has 10 characters, not the 12 you
indicated in your first post.)

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

Thanks thats what i was looking for!

One other question..

how do i eplace a number?

for example after i removed the unwanted digits how do i replace say the
first number "2" with a number "1"

E.G Change 23456 to 12345...

Many thanks!

Bill

Perhaps Text-to-Columns?

If all cell values are 12 characters in length
try something like this:

Select the single-column range of cells

Then...from the Excel main menu:
<data><text-to-columns>
Check: Fixed width..........click [Next]
Click between the 2nd and 3rd characters (to insert a break point)
Click between the 9th and 10th characters (to insert another break point)
Click [Next]
Click on the 1st of the 3 columns....Check: Do not import column
Click on the 3rd of the 3 columns....Check: Do not import column
Click [Finish]

That will lop off the 1st 2 and the last 3 characters

(BTW....Your example of H016690180 only has 10 characters, not the 12 you
indicated in your first post.)

Does that help?
***********
Regards,
Ron

XL2002, WinXP

thanks for the response david.

let me explain a little further..

For example... currently I have the following in cell A1... H016690180

I wish to remove the H0 from the beginning and the 180 from the end....

Is this possible to have excel perform this automatically?

thanks again,

Bill

 

[Dave Peterson]

Change any first digit to 1?

=--("1"&MID(A1,2,255))

The -- converts the string back to a number.
The 255 is a number that's big enough to cover the rest of the string.

Thanks thats what i was looking for!

One other question..

how do i eplace a number?

for example after i removed the unwanted digits how do i replace say the
first number "2" with a number "1"

E.G Change 23456 to 12345...

Many thanks!

Bill

Perhaps Text-to-Columns?

If all cell values are 12 characters in length
try something like this:

Select the single-column range of cells

Then...from the Excel main menu:
<data><text-to-columns>
Check: Fixed width..........click [Next]
Click between the 2nd and 3rd characters (to insert a break point)
Click between the 9th and 10th characters (to insert another break point)
Click [Next]
Click on the 1st of the 3 columns....Check: Do not import column
Click on the 3rd of the 3 columns....Check: Do not import column
Click [Finish]

That will lop off the 1st 2 and the last 3 characters

(BTW....Your example of H016690180 only has 10 characters, not the 12 you
indicated in your first post.)

Does that help?
***********
Regards,
Ron

XL2002, WinXP

thanks for the response david.

let me explain a little further..

For example... currently I have the following in cell A1... H016690180

I wish to remove the H0 from the beginning and the 180 from the end....

Is this possible to have excel perform this automatically?

thanks again,

Bill

:

=MID(A1,4,7) and copy down.
--
David Biddulph

Hi,

i'm not sure how to explain this but here goes...

i have cells containing 12 digit numbers. I need to remove the first 3 and
the last 2 digits. As there are 1000 plus cells (all in the same colum)
which
i need this to occur is there a way to automate this inexcel?

thanks,

Bill

 

[David Biddulph]

You asked for a formula to remove the first 3 and the last 2 digits from a
12 digit number.
Now you seem to have changed your mind and want to remove the first 2 and
last 3 characters from a 10 character string.
In that case instead of =MID(A1,4,7) you'll need =MID(A1,3,5).
If you don't know that your string is going to be 10 characters, but want to
remove the 1st 2 and last 3 from a string of unknown length, then use
=MID(A1,3,LEN(A1)-5)

 

[David Biddulph]

Again, as with your previous question, you need to make up your mind what
you want.
If you replace the number 2 in "23456" with a number 1, you won't get
"12345", but "13456".
Do you want to replace all 2s by 1s, or only the first occurrence of 2, or
only if it occurs as the first character in the string? The functions to
look at are SUBSTITUTE and REPLACE. Excel help will tell you how they are
used.
--
David Biddulph

Thanks thats what i was looking for!

One other question..

how do i eplace a number?

for example after i removed the unwanted digits how do i replace say the
first number "2" with a number "1"

E.G Change 23456 to 12345...

Many thanks!

Bill

Perhaps Text-to-Columns?

If all cell values are 12 characters in length
try something like this:

Select the single-column range of cells

Then...from the Excel main menu:
<data><text-to-columns>
Check: Fixed width..........click [Next]
Click between the 2nd and 3rd characters (to insert a break point)
Click between the 9th and 10th characters (to insert another break point)
Click [Next]
Click on the 1st of the 3 columns....Check: Do not import column
Click on the 3rd of the 3 columns....Check: Do not import column
Click [Finish]

That will lop off the 1st 2 and the last 3 characters

(BTW....Your example of H016690180 only has 10 characters, not the 12 you
indicated in your first post.)

Does that help?
***********
Regards,
Ron

XL2002, WinXP

thanks for the response david.

let me explain a little further..

For example... currently I have the following in cell A1... H016690180

I wish to remove the H0 from the beginning and the 180 from the end....

Is this possible to have excel perform this automatically?

thanks again,

Bill

:

=MID(A1,4,7) and copy down.
--
David Biddulph

Hi,

i'm not sure how to explain this but here goes...

i have cells containing 12 digit numbers. I need to remove the
first 3 and
the last 2 digits. As there are 1000 plus cells (all in the same
colum)
which
i need this to occur is there a way to automate this inexcel?

thanks,

Bill