Extracting value using vlookup from a cell

superboy

Board Regular
Joined
Mar 31, 2013
Messages
52
Hello,

In cell A2 I have "Product name, listing id, Qty: 2" and In cell B2, I will like to be able to use Vlookup or any function to extract Qty :2. I want cell B2 to reflect the quantity 2. I get the feeling this is relatively easy to do but I am self thought and I will appreciate any help. Thank you.
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,719
Office Version
  1. 365
Platform
  1. Windows
The formulas below assume that the value is always at the end of your text string.
If the value will never be over 2 digits you could use the formula in C1.

If the value could be over 2 digits then use the formula in B1 (this formula assumes that the value will have Qty: in front of it.

Excel Workbook
ABCD
1Product name, listing id, Qty: 222
Sheet2
 

István Hirsch

Well-known Member
Joined
May 16, 2013
Messages
1,634
Try this formula if the number is at the end of the string and is not higher than 10^10:

=LOOKUP(10^10,1*RIGHT(A1,{1,2,3,4,5,6,7,8,9,10}))
 

superboy

Board Regular
Joined
Mar 31, 2013
Messages
52
Hello,

I will first and foremost like to thank you all for your help in solving this issue. I have but one question on how to edit your function. The example given to you was an example but for something like the data below.

How will I edit this =RIGHT(A1,LEN(A1)-SEARCH("Qty:",A1)-4)+0
I tried on my own but failed, I could only remove the "+0" and get the quantity value but it came with the asin and sku too


111-111111-11111
SONY REMOTE
QTY: 1 ASIN: B000000000 SKU: 00-0000-0000
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,719
Office Version
  1. 365
Platform
  1. Windows
See if this works for you.

Excel Workbook
ABC
1SONY REMOTE QTY: 1 ASIN: B000000000 SKU: 00-0000-00001
2
Sheet1
 

Watch MrExcel Video

Forum statistics

Threads
1,122,421
Messages
5,596,033
Members
414,039
Latest member
southike

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top