If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#18
|
|||
|
|||
Extract Numerics only
A little more info on my problem:
This formula works: =IF(VALUE(RIGHT(C3381,3))=107,7,IF(LEFT(N3381,8)=" OFF ROAD","MANUAL INPUT",VALUE(LEFT(LOOKUP(REPT("z",255),MID(N3381,M IN(SEARCH({0,1,2,3,4,5,6,7,8,9},N3381&"0123456789" )),{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15})),2)))) By adding to more conditions to the front of it, I get an error: =IF(N3381="M8",35,IF(VALUE(RIGHT(C3881,3))=814,33, IF(VALUE(RIGHT(C3381,3))=107,7,IF(LEFT(N3381,8)="O FF ROAD","MANUAL INPUT",VALUE(LEFT(LOOKUP(REPT("z",255),MID(N3381,M IN(SEARCH({0,1,2,3,4,5,6,7,8,9},N3381&"0123456789" )),{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15})),2)))))) "Corey" wrote: This works perfect Peo! Thanks a bunch! Also to you Domenic for coming up with that original formula. I tried Bob's suggestion to this as well, but got #VALUE!. Since your formula returns the everything to the right, starting with the first numeric, I just added VALUE(LEFT(A1,2)) to pull out the first two numbers. This is the division number I've been trying to get at. Perhaps if I said that earlier, there's another way to get it done. ?? Anyways, I've also added two if statements to the beginning of that and it works great. When I try to add a third I get an error that leads me to the "SEARCH" portion of the formula. Is there a limit to how many conditionals I can put in this thing? Thanks again! "Peo Sjoblom" wrote: The reason the first returns 769142 is that the extracted value 11-4005 is seen as a date so Excel translates the date as 11/01/05 which is 769142 days after Jan 0 1900 Excel's first date, given that the numbers can be so different it is hard to guard against, try this adaptation of Domenic's excellent formula =LOOKUP(REPT("z",255),MID(A1,MIN(SEARCH({0,1,2,3,4 ,5,6,7,8,9},A1&"0123456789 ")),{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15})) -- Regards, Peo Sjoblom "Corey" wrote in message ... Thanks for all the suggestions people. I'm not to familiar with VBA nor UDF. Also, I'm trying to put together a tool for a below novice user of Excel as it takes her hours to decipher a statement. I was hoping to use just a simple formula and have her cut and paste the data to have it automatically populate with this formula. I've tried yours Domenic, and it seems to work for the most part, except for some: OFF 11-4005 returns 769142 ON 040652 returns 40652 (losing the first 0) "Domenic" wrote: If your numbers are not more than 15 digits in length, try... =LOOKUP(9.99999999999999E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A 1&"0123456789")),{1,2,3,4,5,6,7,8,9,10,11,12,13,14 ,15})) Otherwise, try... =LOOKUP(9.99999999999999E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A 1&"0123456789")),ROW(INDIRECT("1:"&LEN(A1))))) Hope this helps! In article , "Corey" wrote: Hello. I have a column with data that contain numerics and text: ON 3127 ON2679 O/F 20R OFF///130H Is there a formula I can input in an adjacent cell to only pull out the numbers to give me the following: 3127 2679 20 130 Any help is appreciated. Thanks! |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
a tip about how to extract .SWFfile from PowerPoint file | SusanZheng | Powerpoint | 1 | November 3rd, 2005 03:58 AM |
Extract records with a specific field appearing more than once in the DB | markx | Running & Setting Up Queries | 7 | September 28th, 2005 06:44 PM |
Trendline Extract | Phil Hageman | Charts and Charting | 5 | July 6th, 2005 02:27 AM |
Extract Unique Values, Then Extract Again to Remove Suffixes | Karl Burrows | General Discussion | 23 | June 25th, 2005 10:37 PM |
Extract specific data into its own workbook via macro? | Adrian B | General Discussion | 2 | February 24th, 2005 06:09 AM |