Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing unwanted digits
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing unwanted digits
=MID(A1,4,7) and copy down.
-- David Biddulph "bill" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing unwanted digits
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 "David Biddulph" wrote: =MID(A1,4,7) and copy down. -- David Biddulph "bill" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing unwanted digits
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 "bill" wrote: 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 "David Biddulph" wrote: =MID(A1,4,7) and copy down. -- David Biddulph "bill" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing unwanted digits
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 "Ron Coderre" wrote: 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 "bill" wrote: 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 "David Biddulph" wrote: =MID(A1,4,7) and copy down. -- David Biddulph "bill" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing unwanted digits
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. bill wrote: 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 "Ron Coderre" wrote: 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 "bill" wrote: 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 "David Biddulph" wrote: =MID(A1,4,7) and copy down. -- David Biddulph "bill" wrote in message ... 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 -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing unwanted digits
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 "bill" wrote in message ... 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 "David Biddulph" wrote: =MID(A1,4,7) and copy down. -- David Biddulph "bill" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing unwanted digits
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 "bill" wrote in message ... 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 "Ron Coderre" wrote: 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 "bill" wrote: 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 "David Biddulph" wrote: =MID(A1,4,7) and copy down. -- David Biddulph "bill" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Removing unwanted characters | Excel Discussion (Misc queries) | |||
Removing unwanted rows | Excel Worksheet Functions | |||
Removing unwanted characters | Excel Discussion (Misc queries) | |||
Removing unwanted characters | Excel Discussion (Misc queries) | |||
removing unwanted macros... | Excel Discussion (Misc queries) |