How to extract specific data from a cell

[shlomek12]

I am looking to get out from all cells the letters I made in buld out from this cell

https://www.screencast.com/t/apAeD0FCrE6

[TABLE="width: 723"]
<tbody>[TR]
[TD]walgreens.com/store/c/as-seen-on-tv-roll-a-lotion-applicator/ID=prod6095201-product[/TD]
[/TR]
[TR]
[TD]walgreens.com/store/c/the-ove-glove-hot-surface-handler-oven-mitt/ID=prod6021452-product[/TD]
[/TR]
[TR]
[TD]walgreens.com/store/c/finishing-touch-lumina-personal-hair-remover/ID=prod2436909-product

[/TD]
[/TR]
</tbody>[/TABLE]

 

[shlomek12]

here I need everything after /

The goal here is to get the NUMBER of how many there is in box,maybe we need an if statement depending where the/ is

maybe you need the excel file?

 

[jtakw]

This updated formula will take care of All the possibilities you showed in your picture:

Book1
	A	B
1	AIR WICK AIR FRESHENER 12/8 oz SNUGGLE FRESH LINEN	12
2	AIR WICK AIR FRESHENER 12/8Z APPLE CINNAMON MEDLEY	12
3	AIR WICK AIR FRESHENER 12/8Z FRESH WATERS	12
4	AIR WICK AIR FRESHENER 12/8Z LAVENDER&CHAMOMILE	12
5	AIR WICK AIR FRESHENER 8/8Z LAVENDER&CHAMOMILE	8
6	AIR WICK AIR FRESHENER 111/8Z MAGNOLIA & CHERRY BLOSSOM	111
7	AIR WICK AIR FRESHENER 12/8Z MAGNOLIA & CHERRY BLOSSOM	12
8	AIR WICK AIR FRESHENER 8 oz MAGNOLIA & CHERRY BLOSSOM CS/12	12
9	AIR WICK AIR FRESHENER 8 oz MAGNOLIA/CHERRY BLOSSOM CS/12	12
10	AIR WICK AIR FRESHENER 12/8Z RAIN GARDEN	12
11	AIR WICK AIR FRESHENER 12/8Z VANILLA INDULGENCE	12
12	AIR WICK AIR FRSHNR 12/8Z HAWAI'I EXOTIC PAPAYA & HIBISCUS FLWR	12
Sheet72

Cell Formulas
Range	Formula
B1	=RIGHT(IF(ISNUMBER(RIGHT(A1,1)+0),SUBSTITUTE(A1,"/",REPT(" ",15)),SUBSTITUTE(LEFT(A1,FIND("/",A1)-1)," ",REPT(" ",15))),15)+0

Formula copied down.

 

[shlomek12]

wow ,this works wonder ,thank you very much

but... sometimes there is no / , so we need to add please an if statement that if there is no /, then number should be

here is a sample:


[TABLE="width: 620"]
<tbody>[TR]
[TD]Baby Needs: HEALTH SMART BABY OIL 8 OZ

or sometimes the have the / but it's not related to a number

like this:
[TABLE="width: 620"]
<tbody>[TR]
[TD]Baby Needs: HEALTH SMART BABY OIL W/COCOA BUTTER 8 OZ

thanks in advenced[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

here I need everything after /

The goal here is to get the NUMBER of how many there is in box,maybe we need an if statement depending where the/ is

maybe you need the excel file?

wow ,this works wonder ,thank you very much

but... sometimes there is no / , so we need to add please an if statement that if there is no /, then number should be

here is a sample:


[TABLE="width: 620"]
<tbody>[TR]
[TD]
Baby Needs: HEALTH SMART BABY OIL 8 OZ

or sometimes the have the / but it's not related to a number

like this:
[TABLE="width: 620"]
<tbody>[TR]
[TD]Baby Needs: HEALTH SMART BABY OIL W/COCOA BUTTER 8 OZ

thanks in advenced
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

In your latest samples where, there are No data indicating how Many is in a Box, and your post says "should be", then nothing?
My updated formula will leave the cells Blank in those cases.

Book1
	A	B
1	AIR WICK AIR FRESHENER 12/8 oz SNUGGLE FRESH LINEN	12
2	AIR WICK AIR FRESHENER 12/8Z APPLE CINNAMON MEDLEY	12
3	AIR WICK AIR FRESHENER 12/8Z FRESH WATERS	12
4	AIR WICK AIR FRESHENER 12/8Z LAVENDER&CHAMOMILE	12
5	AIR WICK AIR FRESHENER 8/8Z LAVENDER&CHAMOMILE	8
6	AIR WICK AIR FRESHENER 111/8Z MAGNOLIA & CHERRY BLOSSOM	111
7	AIR WICK AIR FRESHENER 12/8Z MAGNOLIA & CHERRY BLOSSOM	12
8	AIR WICK AIR FRESHENER 8 oz MAGNOLIA & CHERRY BLOSSOM CS/12	12
9	AIR WICK AIR FRESHENER 8 oz MAGNOLIA/CHERRY BLOSSOM CS/12	12
10	Baby Needs: HEALTH SMART BABY OIL 8 OZ
11	Baby Needs: HEALTH SMART BABY OIL W/COCOA BUTTER 8 OZ
12	AIR WICK AIR FRESHENER 12/8Z RAIN GARDEN	12
13	AIR WICK AIR FRESHENER 12/8Z VANILLA INDULGENCE	12
14	AIR WICK AIR FRSHNR 12/8Z HAWAI'I EXOTIC PAPAYA & HIBISCUS FLWR	12
Sheet72

Cell Formulas
Range	Formula
B1	=IFERROR(RIGHT(IF(ISNUMBER(RIGHT(A1,1)+0),SUBSTITUTE(A1,"/",REPT(" ",15)),SUBSTITUTE(LEFT(A1,FIND("/",A1)-1)," ",REPT(" ",15))),15)+0,"")

 

[shlomek12]

Thank you very much for everything

Can you please fix this also? I am getting the results of number 1 This is the formula I use:


[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G25[/TH]
[TD="align: left"]=IFERROR(RIGHT(IF(ISNUMBER(RIGHT(E25,1)+0),SUBSTITUTE(E25,"/",REPT(" ",15)),SUBSTITUTE(LEFT(E25,FIND("/",E25)-1)," ",REPT(" ",15))),15)+0,1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Excel 2010

	D
RIGHT GUARD IS AP-DEO XTRM DRY RUSH 2.6 OZ CS/12 (96 HR)
NIAGARA SPRAY STARCH PLUS HEAVY 20 oz CS/12 PP$1.59
TIDE DETERGENT POWDER W/DOWNY 720 GRAM CS/18 (BAG)
WINDEX REGULAR BLUE 32 oz CS/12 TRIGGER
COLGATE TB PREMIER CLEAN CARD OF 12 CS/24 (288pcs)
KLEENEX TISSUES 90 CT CS/64 FAMILY
KLEENEX TISSUES 90 CT CS/64 FLORAL
DOVE CARING HAND WASH CUCUMBER & GRN TEA 250 ML CS/12 (PUMP)
DOVE CARING HAND WASH CUCUMBER & GRN TEA 250 ML CS/6 (PUMP)
DOVE CARING HAND WASH FINE SILK 250 ML CS/6 (PUMP)
DOVE CARING HAND WASH ORIGINAL 250 ML CS/6 (PUMP)
DOVE CARING HAND WASH SHEA BUTTER 250 ML CS/6 (PUMP)

<tbody>
[TD="align: center"]372[/TD]

[TD="align: center"]712[/TD]

[TD="align: center"]747[/TD]

[TD="align: center"]759[/TD]

[TD="align: center"]789[/TD]

[TD="align: center"]865[/TD]

[TD="align: center"]866[/TD]

[TD="align: center"]1142[/TD]

[TD="align: center"]1143[/TD]

[TD="align: center"]1144[/TD]

[TD="align: center"]1145[/TD]

[TD="align: center"]1146[/TD]

</tbody>

HBA_List_Report_Excel​

 

[jtakw]

These data/text string extraction formulas are NOT universal, you are spitting out different types of data strings with every post, what you should do is post all the various data strings you have, and MAY BE a single formula can handle all of them - thou May Not.

This formula will work for your latest sample set, but will not work for the others you posted previously:

Book1
	D	E
1	RIGHT GUARD IS AP-DEO XTRM DRY RUSH 2.6 OZ CS/12 (96 HR)	12
2	NIAGARA SPRAY STARCH PLUS HEAVY 20 oz CS/12 PP$1.59	12
3	TIDE DETERGENT POWDER W/DOWNY 720 GRAM CS/18 (BAG)	18
4	WINDEX REGULAR BLUE 32 oz CS/12 TRIGGER	12
5	COLGATE TB PREMIER CLEAN CARD OF 12 CS/24 (288pcs)	24
6	KLEENEX TISSUES 90 CT CS/64 FAMILY	64
7	KLEENEX TISSUES 90 CT CS/64 FLORAL	64
8	DOVE CARING HAND WASH CUCUMBER & GRN TEA 250 ML CS/12 (PUMP)	12
9	DOVE CARING HAND WASH CUCUMBER & GRN TEA 250 ML CS/6 (PUMP)	6
10	DOVE CARING HAND WASH FINE SILK 250 ML CS/6 (PUMP)	6
11	DOVE CARING HAND WASH ORIGINAL 250 ML CS/6 (PUMP)	6
12	DOVE CARING HAND WASH SHEA BUTTER 250 ML CS/6 (PUMP)	6
Sheet72

Cell Formulas
Range	Formula
E1	=LEFT(SUBSTITUTE(MID(D1,SEARCH("CS/",D1)+3,255)," ",REPT(" ",100)),100)+0

Formula copied down.

 

[shlomek12]

Is there any option to make this universal?

Since I see many cells with different formats?

This is a big table with many lines on it

Any idea?

 

[jtakw]

These data/text string extraction formulas are NOT universal, you are spitting out different types of data strings with every post, what you should do is post all the various data strings you have, and MAY BE a single formula can handle all of them - thou May Not.

Is there any option to make this universal?

Since I see many cells with different formats?

This is a big table with many lines on it

Any idea?

In Green, YES, see Red above.
In Orange, DONT KNOW, see Red above.

Make a post of all the different types/formats of Text strings in your data, we'll see what we can do.

 

[jtakw]

shlomek12,

As stated in my post above, you Really need to post a list of samples containing all the possible formats of your data, Again, these formulas are Not universal.

For your latest request posted at a different thread here: https://www.mrexcel.com/forum/excel...g-multiple-individual-functions-into-one.html

Book1
	A	B
16	http://www.samsclub.com/sams/nutri-ninja-blender-bl681a/prod20410492.ip?xid=plp_product_1_2	prod20410492
17	http://www.samsclub.com/sams/shark-ion-robot-rv720/prod22210179.ip?xid=plp_product_1_1	prod22210179
18	http://www.samsclub.com/sams/richmond-foosball-58-inch-soccer/prod21930008.ip?xid=plp_product_1_2	prod21930008
19	http://www.samsclub.com/sams/shower-chair-with-armrests/prod20052125.ip?xid=plp_product_1_3	prod20052125
20	http://www.samsclub.com/sams/transfer-bench/prod20132231.ip?xid=plp_product_1_5	prod20132231
21	http://www.samsclub.com/sams/twst-clrpencil-ast30-twistables-colored/prod19260226.ip?xid=plp_product_1_5	prod19260226
22	http://www.samsclub.com/sams/cyo-wash-chalk-48ast-wshbl-sidewalk-chalk/prod18520296.ip?xid=plp_product_1_1	prod18520296
23	http://www.samsclub.com/sams/4-geartrack-2-pack-panels-trim/prod16820488.ip?xid=plp_product_1_1	prod16820488
24	http://www.samsclub.com/sams/storage-bin-holder-wall-components/prod16820491.ip?xid=plp_product_1_54	prod16820491
Sheet48

Cell Formulas
Range	Formula
B16	=TRIM(LEFT(RIGHT(SUBSTITUTE(SUBSTITUTE(LEFT(A16,FIND("?",A16&"?")-1),".","/"),"/",REPT(" ",100)),200),100))