Are all strings to process similar to the string you provided, for example: "12-345 this is (text) 56"? If so, you do not need to use that general formula to solve the problem.
Greetings Ron,
I found this information quite useful solving an issue I had. However, I have run into som problems.
I am using the formula below extracting numbers from a string formated something like this: NUMBER-NUMBER TEXT TEXT (TEXT) NUMBER
However, when the first bracket of texts first three letters resembles that of a month (example given: MARCONIAN) the formual returns a date in number value (41712) and not the actualy two-digit number.
I expect the formual need to change format somehow, but I fail to locate how/where.
I have tried altering format in both the source and the target cell.
Formula: =HVISFEIL(SLÅ.OPP(10^99;--DELTEKST("|"&J15;N.MINST(HVIS(((--ERTALL(--("0"&DELTEKST(BYTT.UT(" "&J15;" ";"|");RAD($1:$60);1)))=0)*ERTALL(--(DELTEKST(BYTT.UT(" "&J15;" ";"|");RAD($2:$61);1))));RAD($2:$61));2);RAD($1:$60)));" ")
(Formula is translated to localized language, the original formula below)
=IFERROR(LOOKUP(10^99,--MID("|"&A1,SMALL(IF(((--ISNUMBER(--("0"&MID(SUBSTITUTE(" "&A1," ","|"),ROW($1:$25),1)))=0)*ISNUMBER(--(MID(SUBSTITUTE(" "&A1," ","|"),ROW($2:$26),1)))),ROW($2:$26)),C1),ROW($1:$25))),””)
Are all strings to process similar to the string you provided, for example: "12-345 this is (text) 56"? If so, you do not need to use that general formula to solve the problem.
No, they are not. Due to sloppy input there are spaces (" ") in random locations on a number of the entries.
We have looked into various solutions for this, but the amount of data is vast, and thus far this is the only formula that extracts the numbers with a relative precision.
The only information I am interessted in is recorded like this (where X, Y and Z being number values) XXX-YY ZZZZ. The entry can contain any number of spaces and or characters anywhere in the cell, however it will never split the number values.
Alternatively I could return all 3-digit numbers in first column, then return all 2-digit numbers in second and last all 4-digit numbers in last column, but I would not know how to accomplish this.
Will this formula work for an alphanumeric string longer than 14 characters? If not, are you aware of any function that will provide only the numerical values from the following string?
2297244910LA172/165
I'd like for the function to return a value of 2297244910172165 in the example above.
Tks. dhaile
=========================================
Hello Experts,
With regarding to this post i have very simple query in front if you, hope will get great help.
very simple--
abc123
123abc
a123b
12bnm12
a12bn
Looking for only numbers in next cell. Will this possible. if yes, please post the help.
[QUOTE=Ron Coderre;2193647]Try this:
Use this :
=SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*ROW(INDIRECT("1:"&LEN(A1))),0,1),ROW(INDIRECT("1:"&LEN(A1))))+1,1)*10^(ROW(INDIRECT("1:"&LEN(A1)))-1))
Hi, I have searched this thread but so far none of the formulas are working. Perhaps I am to new to excel and have an error in cut/paste? Anyways I am looking for a formula to extract only the numbers. So for example a part number BROTHER HL-2270DW and apply formula so output is only "2270". Any suggestion?
Rick's "mini" blog... http://www.excelfox.com/forum/f22/
.
Want to post a small screen shot? See Part B here.
Like this thread? Share it with others