• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Numeric data find out through excel

but if i wanna only no

like asdfasdfa456325asdfasdf236
and output should be 456325236
then how can i extract from which formula?
 
Hi,

I like the Deepak's solution but was not able to understand them quite well. My bad :(.

At the same time, for your question, you can extend D's solution with & and repeat the formula, with substituing A2 with same formula again.

=--MID(A2,MATCH(TRUE,ISNUMBER(--MID(A2,ROW(1:99),10)),0),10) & --MID(SUBSTITUTE(A2,--MID(A2,MATCH(TRUE,ISNUMBER(--MID(A2,ROW(1:99),10)),0),10),""),MATCH(TRUE,ISNUMBER(--MID(SUBSTITUTE(A2,--MID(A2,MATCH(TRUE,ISNUMBER(--MID(A2,ROW(1:99),10)),0),10),""),ROW(1:99),10)),0),10)

Entered as CSE.

I know this is nasty, :( , until you find better solution from D or other Excel Experts.

Regards,
Prasad DN
PS: the key point is in your data set, the first set of number should be 10 digits, and second set can be upto 10 digits.
Also, the number should not be exactly same.
 
Perhaps, though that thread doesn't mention Lori's solution using NPV, which I would argue is the preferable choice in such cases.

I give a version of the formula and an explanation here:

http://excelxor.com/2014/11/01/extracting-numbers-from-a-string-4-all-numbers-to-a-single-cell/

although I should say that I would now prefer a less volatile choice for the ROW construction, i.e. (CSE):

=NPV(-0.9,IFERROR(MID(A1,1+LEN(A1)-ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1)/10,""))

Regards
 
XOR LX said:
Perhaps, though that thread doesn't mention Lori's solution using NPV, which I would argue is the preferable choice in such cases.

I give a version of the formula and an explanation here:

http://excelxor.com/2014/11/01/extracting-numbers-from-a-string-4-all-numbers-to-a-single-cell/

although I should say that I would now prefer a less volatile choice for the ROW construction, i.e. (CSE):

=NPV(-0.9,IFERROR(MID(A1,1+LEN(A1)-ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1)/10,""))

Regards
Click to expand...
Hi Xor LX,

amazing technique, I have gone through your link.

Now, I have one question to you. How do you even think of such solutions? And connecting Financial solution oriented formula NPV to such situation!! Brilliant!!

Regards,
Prasad DN
 
@Narayan

Understood, though it can't hurt to remind ourselves of such a solution: whilst impressive, those recommended in that older post seem rather inelegant when seen in the same light as the NPV set-up.

It might also be worth noting that, in my version, omission of the value1 parameter is not mandatory.

Regards
 
@prasaddn

Thanks, though all I did was to give an explanation as to how the formula works.

It's Lori to whom you should be giving your praise.

Regards
 
Hi,

The formula depends on what type of data is.

NPV function does quite magic in this regard.

For shorting the same.

=NPV(-0.9,IFERROR(MID(A1,1+LEN(A1)-ROW($1:$999),1)/10,""))
=NPV(-0.9,,IFERROR(MID(A1,1+LEN(A1)-ROW($1:$999),1)%,""))

=(MID(A2,MATCH(TRUE,ISNUMBER(--MID(A2,ROW($1:$99),1)),0),6)&MID(A2,MATCH(TRUE,ISNUMBER(--MID(A2,ROW($1:$99),10)),0),10))+0

All are Array functions.

If the input is dynamic then i suggest to go with NPV.
 
@Deepak, yes in practise string length is usually limited and in that case we might go a step further and remove references and array entry too, eg (<100 chars):

=NPV(-0.9,,INDEX(IFERROR(--MID(A1,10*{9;8;7;6;5;4;3;2;1;0}+{9,8,7,6,5,4,3,2,1,0},1),""),))%

This method was originally just posted as a passing observation and it was quite unexpected to see the subsequent interest it has generated. The fact we need to come up with such roundabout solutions is a consequence of there not being any significant new text functions in the application in decades.
 
Hi all,

I think this would be quite enough...

{=NPV(-0.9,IFERROR(MID(A1,256-ROW($1:$255),1)/10,""))}

NPV will only used when all text have to flushed.

Else formula's will depends on output.
 
For your first point, using 256-ROW($1:$255) is clearly shorter and quicker to construct (in the original 2009 google groups post i suggested a similar approach) but there's a couple of things to be aware of with that:

1. Inserting or deleting rows causes the range in ROW($1:$255) to change
2. Changing any cell in the first 255 rows will cause all such formulas to recalculate due to the formula referencing the entire range.

So it's probably a good idea to paste as values after.

The alternative i suggested is a little longer but is a normal formula so it doesn't need array entry. Also since there are no other range dependencies it only recalculates when A1 is changed and is robust to any sheet editing. To extend the array up to 256 values it would be preferable to use a defined name with numbers up to 16 or else in 2013 version use 256-TREND(MUNIT(16)*0+171,,,0) for the same result.

For your second point - agreed. There's an English saying: "horses for courses" (in this context roughly translates to using the appropriate tool for the given output.)
 
Some suggested alternatives are ...

=NPV(-0.9,IFERROR(MID(A1,256-ROW(OFFSET(A1,0,0,255,1)),1)/10,""))
=NPV(-0.9,IFERROR(MID(A1,256-ROW(INDIRECT("1:255")),1)/10,""))

Inserting/deleting rows/columns would not affect the same & works with almost all version of the xl.

Well, Excel is excellent in itself so lot's of possibles to play with it.
 
@Deepak

Perhaps, but then both of those are fully volatile, something which, if we can avoid without too much effort, we probably should.

Regards
 
You must log in or register to reply here.
Back
Top