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?



[TABLE="width: 501"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]RAW DATA[/TD]
[TD][/TD]
[TD]What I want the formula to provide [/TD]
[/TR]
[TR]
[TD]100-19-907-BT[/TD]
[TD][/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]125-03-1266-BT[/TD]
[TD][/TD]
[TD]125[/TD]
[/TR]
[TR]
[TD]149-04-1252-BT[/TD]
[TD][/TD]
[TD]149[/TD]
[/TR]
[TR]
[TD]188-13-908-BT[/TD]
[TD][/TD]
[TD]188[/TD]
[/TR]
[TR]
[TD]316-20-912-BT[/TD]
[TD][/TD]
[TD]316[/TD]
[/TR]
[TR]
[TD]49-01-1213-BT[/TD]
[TD][/TD]
[TD]49[/TD]
[/TR]
[TR]
[TD]740-04-1263-BT[/TD]
[TD][/TD]
[TD]740[/TD]
[/TR]
[TR]
[TD]762-02-1285-BT[/TD]
[TD][/TD]
[TD]762[/TD]
[/TR]
[TR]
[TD]790-02-915-BT[/TD]
[TD][/TD]
[TD]790[/TD]
[/TR]
[TR]
[TD]817-01-BT[/TD]
[TD][/TD]
[TD]817[/TD]
[/TR]
[TR]
[TD]818-01-906-BT[/TD]
[TD][/TD]
[TD]818[/TD]
[/TR]
[TR]
[TD]SOW-100-003-BT[/TD]
[TD][/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]SOW-645-002-BT[/TD]
[TD][/TD]
[TD]645[/TD]
[/TR]
</tbody>[/TABLE]

 

[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...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr]
[tr][td]

1​

[/td][td]RAW DATA[/td][td]Yours[/td][td]Mine[/td][/tr]

[tr][td]

2​

[/td][td]100-19-907-BT[/td][td]100[/td][td]100[/td][/tr]

[tr][td]

3​

[/td][td]125-03-1266-BT[/td][td]125[/td][td]125[/td][/tr]

[tr][td]

4​

[/td][td]149-04-1252-BT[/td][td]149[/td][td]149[/td][/tr]

[tr][td]

5​

[/td][td]188-13-908-BT[/td][td]188[/td][td]188[/td][/tr]

[tr][td]

6​

[/td][td]316-20-912-BT[/td][td]316[/td][td]316[/td][/tr]

[tr][td]

7​

[/td][td]49-01-1213-BT[/td][td]49[/td][td]49[/td][/tr]

[tr][td]

8​

[/td][td]740-04-1263-BT[/td][td]740[/td][td]740[/td][/tr]

[tr][td]

9​

[/td][td]762-02-1285-BT[/td][td]762[/td][td]762[/td][/tr]

[tr][td]

10​

[/td][td]790-02-915-BT[/td][td]790[/td][td]790[/td][/tr]

[tr][td]

11​

[/td][td]817-01-BT[/td][td]817[/td][td]817[/td][/tr]

[tr][td]

12​

[/td][td]818-01-906-BT[/td][td]818[/td][td]818[/td][/tr]

[tr][td]

13​

[/td][td]SOW-100-003-BT[/td][td]100[/td][td]100[/td][/tr]

[tr][td]

14​

[/td][td]SOW-645-002-BT[/td][td]645[/td][td]645[/td][/tr]
[/table]
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.


[TABLE="class: cms_table, width: 501"]
<tbody>[TR]
[TD]RAW DATA[/TD]
[TD][/TD]
[TD]What I want the formula to provide[/TD]
[/TR]
[TR]
[TD]100-19-907-BT[/TD]
[TD][/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]125-03-1266-BT[/TD]
[TD][/TD]
[TD]125[/TD]
[/TR]
[TR]
[TD]149-04-1252-BT[/TD]
[TD][/TD]
[TD]149[/TD]
[/TR]
[TR]
[TD]188-13-908-BT[/TD]
[TD][/TD]
[TD]188[/TD]
[/TR]
[TR]
[TD]316-20-912-BT[/TD]
[TD][/TD]
[TD]316[/TD]
[/TR]
[TR]
[TD]49-01-1213-BT[/TD]
[TD][/TD]
[TD]49[/TD]
[/TR]
[TR]
[TD]740-04-1263-BT[/TD]
[TD][/TD]
[TD]740[/TD]
[/TR]
[TR]
[TD]762-02-1285-BT[/TD]
[TD][/TD]
[TD]762[/TD]
[/TR]
[TR]
[TD]790-02-915-BT[/TD]
[TD][/TD]
[TD]790[/TD]
[/TR]
[TR]
[TD]817-01-BT[/TD]
[TD][/TD]
[TD]817[/TD]
[/TR]
[TR]
[TD]818-01-906-BT[/TD]
[TD][/TD]
[TD]818[/TD]
[/TR]
[TR]
[TD]SOW-100-003-BT[/TD]
[TD][/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]SOW-645-002-BT

Sale-21-02


Sale-285-15

Sale-569-01[/TD]
[TD][/TD]
[TD]645

21

285

569

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

 

[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