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]

 

[Nine Zero]

use this if they always have 7 digits for the number

in cell B1
=MID(A1,FIND("ID=prod",A1)+7,7)

 

[jtakw]

Hi,

This will handle variable length numbers:

Book1
	A	B	C
1	walgreens.com/store/c/as-seen-on-tv-roll-a-lotion-applicator/ID=prod6095201-product		6095201
2	walgreens.com/store/c/the-ove-glove-hot-surface-handler-oven-mitt/ID=prod6021452-product		6021452
3	walgreens.com/store/c/finishing-touch-lumina-personal-hair-remover/ID=prod2436909-product		2436909
4	walgreens.com/store/c/as-seen-on-tv-roll-a-lotion-applicator/ID=prod609520111-product		609520111
5	walgreens.com/store/c/the-ove-glove-hot-surface-handler-oven-mitt/ID=prod60214-product		60214
Sheet60

Cell Formulas
Range	Formula
C1	=SUBSTITUTE(MID(A1,SEARCH("/ID=prod",A1)+8,255),"-product","")

Formula copied down.

EDIT: you might find this thread helpful: https://www.mrexcel.com/forum/excel...g-multiple-individual-functions-into-one.html

 

[shlomek12]

use this if they always have 7 digits for the number

in cell B1
=MID(A1,FIND("ID=prod",A1)+7,7)

Many Many Thanks to you

 

[shlomek12]

Hi,

This will handle variable length numbers:

	A	B	C
walgreens.com/store/c/as-seen-on-tv-roll-a-lotion-applicator/ID=prod6095201-product
walgreens.com/store/c/the-ove-glove-hot-surface-handler-oven-mitt/ID=prod6021452-product
walgreens.com/store/c/finishing-touch-lumina-personal-hair-remover/ID=prod2436909-product
walgreens.com/store/c/as-seen-on-tv-roll-a-lotion-applicator/ID=prod609520111-product
walgreens.com/store/c/the-ove-glove-hot-surface-handler-oven-mitt/ID=prod60214-product

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"]6095201[/TD]

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

[TD="align: right"][/TD]
[TD="align: right"]6021452[/TD]

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

[TD="align: right"][/TD]
[TD="align: right"]2436909[/TD]

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

[TD="align: right"][/TD]
[TD="align: right"]609520111[/TD]

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

[TD="align: right"][/TD]
[TD="align: right"]60214[/TD]

</tbody>

Sheet60​

[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] "]C1[/TH]
[TD="align: left"]=SUBSTITUTE(MID(A1,SEARCH("/ID=prod",A1)+8,255),"-product","")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Formula copied down.

EDIT: you might find this thread helpful: https://www.mrexcel.com/forum/excel...g-multiple-individual-functions-into-one.html

Thanks you very much

 

[jtakw]

You're welcome.

 

[shlomek12]

[TABLE="width: 347"]
<tbody>[TR]
[TD="width: 347"]How can I remove everything except the last numbers?

[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]https://www.ikea.com/us/en/catalog/products/00015134/[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

I need the number 00015134

Please share a formula with me

Thanks in advance [TABLE="width: 347"]
<tbody>[TR]
[TD="width: 347"][/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

Hi,

Like this:

Book1
	A	B	C
1	https://www.ikea.com/us/en/catalog/products/00015134/		00015134
Sheet69

Cell Formulas
Range	Formula
C1	=TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",100)),200))

 

[shlomek12]

Thanks 1000 times

 

[jtakw]

You're welcome 1001 times.