 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / March 2008need help extracting partial data in a range of cells
if a have a column of cells, say from A1 to A150, and each cell contains a 10 digit number, for example xxx1236xxx, how can I parse, or remove the first 3 digits and the last 3 digits in all the cells? >if a have a column of cells, say from A1 to A150, and each cell contains a >10 digit number, for example xxx1236xxx, how can I parse, or remove the >first 3 digits and the last 3 digits in all the cells? If there will never be a leading zero, then simply: =MID(A1,4,4) and fill down. This will return the value as text, so if there is a leading zero in the four digits being returned, that will be retained. If you wish to convert it to a numeric value, you can precede the formula with a double unary: =--MID(A1,4,4) If there might be leading zero's in your ten digit numbers, then: =MID(TEXT(A1,"0000000000"),4,4) or, numerically: =MOD(INT(A1/10^3),10^4) --ron Try this =MID(A1,4,len(A1)-6))  SignatureHTH, Barb Reinhardt > if a have a column of cells, say from A1 to A150, and each cell contains a > 10 digit number, for example xxx1236xxx, how can I parse, or remove the > first 3 digits and the last 3 digits in all the cells? Hi Walter See if this is what you want, Just for test select "A1" Go to 'Data/Text to Columns/ Select Fixed spaces and fallow the menu. Regards Cimjet > if a have a column of cells, say from A1 to A150, and each cell contains a > 10 digit number, for example xxx1236xxx, how can I parse, or remove the > first 3 digits and the last 3 digits in all the cells? Try =MID(A1,4,4)*1 The Mid formula removes the first and last three digits but the result would be text. The *1 converts it back to a number, Regards, Alan. > if a have a column of cells, say from A1 to A150, and each cell contains a > 10 digit number, for example xxx1236xxx, how can I parse, or remove the > first 3 digits and the last 3 digits in all the cells? Thanks to all for the help. =MID did the trick! > if a have a column of cells, say from A1 to A150, and each cell contains a > 10 digit number, for example xxx1236xxx, how can I parse, or remove the > first 3 digits and the last 3 digits in all the cells? This group continues to impress me to the hilt. Should have made paper copies of notable solutions years ago. The formula used to solve Walter's problem would be very useful to extract NPAs and NXXs from telephone numbers. Wayne > if a have a column of cells, say from A1 to A150, and each cell contains a > 10 digit number, for example xxx1236xxx, how can I parse, or remove the > first 3 digits and the last 3 digits in all the cells?
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.