Extracting Information with Formula

[shahid5788]

Hi,

Can you please assist in providing a formula in which I can extract a part of my raw data line item. Please see below of my raw data and how I want the output to look like. Can you please assist?

RAW DATA		What I want the formula to provide
100-19-907-BT		100
125-03-1266-BT		125
149-04-1252-BT		149
188-13-908-BT		188
316-20-912-BT		316
49-01-1213-BT		49
740-04-1263-BT		740
762-02-1285-BT		762
790-02-915-BT		790
817-01-BT		817
818-01-906-BT		818
SOW-100-003-BT		100
SOW-645-002-BT		645

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[Scott Huish]

For the values that start with text would SOW be the only leading text or are there other possibilities?

If SOW is the only possibility then perhaps:

=LEFT(SUBSTITUTE(SUBSTITUTE(A1,"SOW-",""),"-",REPT(" ",255)),255)+0

 

[FDibbins]

Another option, assuming your current example is representative...

	A​	B​	C​
1​	RAW DATA	Yours	Mine
2​	100-19-907-BT	100	100
3​	125-03-1266-BT	125	125
4​	149-04-1252-BT	149	149
5​	188-13-908-BT	188	188
6​	316-20-912-BT	316	316
7​	49-01-1213-BT	49	49
8​	740-04-1263-BT	740	740
9​	762-02-1285-BT	762	762
10​	790-02-915-BT	790	790
11​	817-01-BT	817	817
12​	818-01-906-BT	818	818
13​	SOW-100-003-BT	100	100
14​	SOW-645-002-BT	645	645

C2=SUBSTITUTE(MID(A2,IF(ISNUMBER(--LEFT(A2,1)),1,5),3),"-","")
copied down

 

[shahid5788]

Hi,

Thank you Scott it looks like your formula works for me currently but I think I have run into another condition. On top of "SOW" I have couple Raw data with another condition. Can you please assist with you formula.

RAW DATA		What I want the formula to provide
100-19-907-BT		100
125-03-1266-BT		125
149-04-1252-BT		149
188-13-908-BT		188
316-20-912-BT		316
49-01-1213-BT		49
740-04-1263-BT		740
762-02-1285-BT		762
790-02-915-BT		790
817-01-BT		817
818-01-906-BT		818
SOW-100-003-BT		100
SOW-645-002-BT Sale-21-02 Sale-285-15 Sale-569-01		645 21 285 569

<tbody>
</tbody>

 

[Scott Huish]

=left(substitute(mid(a1,min(find({1,2,3,4,5,6,7,8,9,0},a1&"1234567890")),255),"-",rept(" ",255)),255)+0