Formula to extract the number

[wrighty998]

<tbody>
</tbody>

please can someone help with these 2 things i need a formula to exctract the number
this one

Delivery Area: 01 - Glasgow / Edinburgh

i need the "01"



and this one

Company Code: 99 & 499 Delivery Yr/Wk: 2018 / 12 Product Category: ALL CATEGORIES

i need the "12"

thanks for any help

 

[Peter_SSs]

Some clarification would help.

1. How do we determine which number(s) you want extracted? Give us some 'rules' in words. (eg The number after "Area:" or the number immediately before "Product Category:" etc)

2. You say you want a number extracted but your first example is "01". "01" is actually text and the number itself is 1. Which do you want as your result?

 

[wrighty998]

Some clarification would help.

1. How do we determine which number(s) you want extracted? Give us some 'rules' in words. (eg The number after "Area:" or the number immediately before "Product Category:" etc)

2. You say you want a number extracted but your first example is "01". "01" is actually text and the number itself is 1. Which do you want as your result?

thankyou for your reply,


input cell

Delivery Area: 01 - Glasgow / Edinburgh

output cell

01

yes please anything after "Area:" and before the "-" for this one



input cell

Company Code: 99 & 499 Delivery Yr/Wk: 2018 / 12 Product Category: ALL CATEGORIES

output cell

12

anything after "/" and before the "Product" for this one

 

[Peter_SSs]

OK, test these.

Excel Workbook
	A	B
1	Delivery Area: 01 - Glasgow / Edinburgh	01
2
3	Company Code: 99 & 499 Delivery Yr/Wk: 2018 / 12 Product Category: ALL CATEGORIES	12
Extract Number

 

[wrighty998]

OK, test these.

Extract Number

	A	B
Delivery Area: 01 - Glasgow / Edinburgh
Company Code: 99 & 499 Delivery Yr/Wk: 2018 / 12 Product Category: ALL CATEGORIES

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:573px;"><col style="width:41px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

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

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

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

</tbody>

Spreadsheet Formulas

Cell Formula B1 =TRIM(REPLACE(LEFT(A1,FIND("-",A1)-1),1,FIND(":",A1),"")) B3 =TRIM(RIGHT(SUBSTITUTE(LEFT(A3,SEARCH("Product",A3)-1),"/",REPT(" ",20)),20)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

thanks thats brill life saver for the amount of info i have at work

 

[Peter_SSs]

thanks thats brill life saver for the amount of info i have at work

Cheers.