• 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.

Extract particular data from a cell

M

manoj_th

Member
Hi,

Please find the attached file and help me how to extract data.

Thanks in advance
Manoj
 

Attachments

  • sample.xlsx
    8.7 KB · Views: 28
Though @Ramesh approach works fine...

There is one more that can be used..

TRIM(RIGHT(SUBSTITUTE(D2,"=",REPT(" ",LEN(D2))),LEN(D2)))

Edit:

Remove the TRIM

RIGHT(SUBSTITUTE(D2,"=",REPT(" ",LEN(D2))),LEN(D2))+0
 
These may...


=TRIM(MID(D2,FIND("~",SUBSTITUTE(D2,"=","~",3))+1,99))+0
=TRIM(RIGHT(D2,LEN(D2)-FIND("~",SUBSTITUTE(D2,"=","~",3))))+0
 
neeraj2050 said:
@ Bosco, thats really cool. Will you mind explaining the logic......
Click to expand...

Formula to return the most right numeric value in a string.

[1] =LOOKUP(9.99999999999999E+307,--RIGHT(D2,ROW($1:$255)))

[2] =-LOOKUP(,-RIGHT(D2,ROW($1:$255)))

Formula [2] is a short form of the original formula [1]

Formula [1] return positive or negative numeric value

Formula [2] return positive numeric value only

Formula [1] & [2] logic is to use Lookup BigNum to return the last numeric value.

Formula [1], the BigNum is 9.99999999999999E+307

Formula [2], the BigNum is blank (or any positive value)

Regards
Bosco
 
Last edited:
@manoj_th thank you for opening this thread.
If you do not mind, I want to ask mr bosco a question.

@bosco_yip
Thank you for sharing your formula & explanation
Sorry I have 1 question.

I thought lookup function will return next largest value.
In the case below, it returns 100.
Should it return 1 because -1 is bigger than -100?

=-LOOKUP(, {-1;-100})
 
@bosco_yip
I tried 0 instead of blank.
It worked also....

=-LOOKUP(0,-RIGHT(D1,ROW($1:$253)))

What I do not understand is why it returns the biggest negative value.
I thought it would return -9 (as it is the biggest in the array)

screenshot.png
 
PP3321 said:
@bosco_yip......
Sorry I have 1 question.
I thought lookup function will return next largest value.
In the case below, it returns 100.
Should it return 1 because -1 is bigger than -100?
=-LOOKUP(, {-1;-100})
Click to expand...

"Lookup function will return next largest value", this statement is wrong.

Should be read as:

"Lookup will ignore the errors and return the LAST numeric item in the array."

For example :

Since we're trying to match blank(0) ,which is larger than any of the calculated numeric values,

=-LOOKUP(, {#VALUE!,#VALUE!,-1,#VALUE!,-100,#VALUE!,#VALUE!})

----> return 100

or,

=-LOOKUP(, {#VALUE!,#VALUE!,-100,#VALUE!,-1,#VALUE!,#VALUE!})

----> return 1

Regards
Bosco
 
Last edited:
PP3321 said:
I thought lookup function will return next largest value.
In the case below, it returns 100.
Should it return 1 because -1 is bigger than -100?

=-LOOKUP(, {-1;-100})
Click to expand...
Hi ,

Let us start with the following formula :

=LOOKUP(100000,0+RIGHT(D2,ROW($1:$255)))

The crucial elements are the three which have been colored.

1. 100000 - When you use the LOOKUP function with a first parameter which is larger in value than any value which can occur within the array ( the second parameter ) , it returns the last element of the array.

2. 0 + ...... This converts all the values which are formed by the array RIGHT function in conjunction with the ROW function , into numbers.

3. ROW($1:$255) ..... This returns an array of numbers from 1 through 255.

When we take the RIGHT function and the ROW function together , and use it on the following data :

ABC 05-11 = 10,798.00
XYZ 05-12 = 10,416.00
QWER 05-12 = 10,416.66

we get an array of values , as follows :

{6;66;0.66;6.66;16.66;416.66;#VALUE!;#VALUE!;10416.66;10416.66;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!......}

What is happening here is that the last digit of the data item is the first element of the above array ; the last 2 digits of the data item are the second element of the above array.

When we come to the comma , the value ,416.66 is not a valid number , and hence it results in a #VALUE! error value.

The next part of the data item , which is 0,416.66 is again not a valid number , and hence it also results in a #VALUE! error value.

When we get to 10,416.66 , we have a valid number again.

All the values to the left of the = sign thereafter result in #VALUE! error values.

Since the LOOKUP function ignores error values , the last valid numeric value which is smaller than 100000 is 10,416.66

----------------------------------------------------------------------------------

If we wish to avoid the use of the 100000 , and the 0+ , we need to do two things :

1. Convert the numbers to negative numbers by prefixing the RIGHT function with the minus sign -.

2. Omit the 100000 since we are no longer looking at positive values. Omitting the first parameter is the same as using 0.

Since all negative numbers will be smaller than 0 i.e. since 0 will be bigger than all negative numbers , the LOOKUP function will still return the last number.

However , because we converted all numbers in the array to negative numbers , we need to now use another minus sign before the overall formula to convert the returned negative number back to a positive number.

Hence :

=-LOOKUP(,-RIGHT(D2,ROW($1:$255)))

Narayan
 
PP3321 said:
@bosco_yip
I tried 0 instead of blank.
It worked also....
=-LOOKUP(0,-RIGHT(D1,ROW($1:$253)))
What I do not understand is why it returns the biggest negative value.
I thought it would return -9 (as it is the biggest in the array)
Click to expand...

1] Excel consider blank equal to 0,

therefore,

=-LOOKUP(,-RIGHT(D1,ROW($1:$255)))

is equal to

=-LOOKUP(0,-RIGHT(D1,ROW($1:$255)))

2] Why LOOKUP return the last numeric values ?

You need to study the searching system of the Excel LOOKUP functions.

2.1] INDEX, and VLOOKUP function using Linear Search, top-down one by one searching until the value is found.

2.2] LOOKUP function using Binary Search, by repeatedly dividing the search interval in half and repeatedly check until the value is found.

Binary search is much faster than linear search for most data sets.

Regards
Bosco
 
@bosco_yip
Thank you for your explanation.
You are right. My knowledge was wrong.
It returns the last value, not the largest value.
Thank you for helping me to learn...

@NARAYANK991
Thank you for your detailed explanation.
It is so clear now...

You guys are really awesome...
I cannot thank you enough for your excellent expertise...
 
You must log in or register to reply here.
Back
Top