Extract Data

[Len]

Hi , is there any simplify and more appropriate method to extract data
from the excel file ? The data need to be extracted starts with "S-06-
XXX or S-07-XXX " and "VW-06-XXX or VW-07-XXX" ( ie XXX denotes any
three digits numeric )
In order to extract the above data correctly I have to go through
excel formula ( ie SUBSTITUTE and LEFT formula ) with several
processes which I find them not practical, tedious and time consuming.
At the end, I still have to modify the text string of each excel
formula to obtain the correct result

Excel file

Columns
A B C D
Date Trans. No Reference No Description
01/01/2007 JNL07-0098 ARR7-00260 SALES,S-06-120,USD924
04/01/2007 ARD7-00016 I07-0001(P) SALES,VW-06-114
04/01/2007 ARD7-00017 I07-0002(P) ORG. S'PORE/S-06-124
04/01/2007 ARD7-00031 EP 323 ORGCORP/S-06-122,USD285.10
04/01/2007 ARD7-00032 EP 324 ORG.CORP/VW-07-068,JPY50,578,500
04/01/2007 ARD7-00033 EP 325 ORGCORP/SALES,S-06-105,JPY179,000
04/01/2007 ARD7-00034 EP 326 ORGINDONESIA/SALES,VW-07-029,USD840
04/01/2007 ARD7-00035 EP 327 ORG S'PORE/SALES,S-07-010
04/01/2007 ARD7-00036 EP 328 ORG S'PORE/CN FOR I06-0117(P),S-06-066
04/01/2007 ARD7-00037 EP 329 ORG THAIL/SALES,VW-07-012,USD962
10/01/2007 JNL07-0001 I07-0011(P) SALES,S-07-012, SHIPPING
CHARGES,USD893
10/01/2007 JNL07-0002 I07-0012(P) TYH001 / VW-06-010
15/01/2007 APD7-00223 DN07-0001(P) AM003 / S-06-099,P07-0039(P)
16/01/2007 ARD7-00043 EP 335 ORG.S'PORE/
S-06-084,P06-0099(OP),USD2,879.40
17/01/2007 JNL07-0003 I07-0016(P) IT001 /
S-06-121,P07-0165(P),JPY107,000
17/01/2007 JNL07-0005 I07-0020(P) Yap LK,T'& Go toll topup,WLN9165,
VW-06-099
17/01/2007 JNL07-0006 I06-0177(P)-R1 Yang LK,Hampers to
customers,S-06-099/121
17/01/2007 JNL07-0034 I06-0176(P)-R1 Chew YM,Acrylic sheet,S-06-098
19/01/2007 ARD7-00231 I07-0021(P) MP006 / Lai KM,ticket & insurance
fee,S-07-002
22/01/2007 APD7-00224 DN07-0002(P) MP006 / Lai KM,KL-BG-KL,
10/3/07,S-07-002
22/01/2007 JNL07-0004 I07-0019(P) Koh PT,SKF bearings for
pump,S-07-035

Thanks for your help
Best Regards
Len

 

[Joel]

Not sure if this better, I think it is more eligant. It works better the
larger the number of items you are searching.

Sub extract()
SearchData = _
Array("S-06-", "S-07-", "VW-06-", "VW-07-")
LastRow = Cells(Rows.Count, "D").End(xlUp).Row

For RowCount = 2 To LastRow
Description = Cells(RowCount, "D")
found = False
For ArrayCount = 0 To (UBound(SearchData))
startchar = InStr(Description, _
SearchData(ArrayCount))
If startchar > 0 Then
Item = Mid(Description, startchar)
Item = Left(Item, _
Len(SearchData(ArrayCount)) + 3)
found = True
Exit For
End If
Next ArrayCount
If found = True Then
'enter your extract code here
Cells(RowCount, "E") = Item
End If
Next RowCount
End Sub

 

[Len]

Not sure if this better, I think it is more eligant. It works better the
larger the number of items you are searching.

Sub extract()
SearchData = _
Array("S-06-", "S-07-", "VW-06-", "VW-07-")
LastRow = Cells(Rows.Count, "D").End(xlUp).Row

For RowCount = 2 To LastRow
Description = Cells(RowCount, "D")
found = False
For ArrayCount = 0 To (UBound(SearchData))
startchar = InStr(Description, _
SearchData(ArrayCount))
If startchar > 0 Then
Item = Mid(Description, startchar)
Item = Left(Item, _
Len(SearchData(ArrayCount)) + 3)
found = True
Exit For
End If
Next ArrayCount
If found = True Then
'enter your extract code here
Cells(RowCount, "E") = Item
End If
Next RowCount
End Sub








- Show quoted text -

Hi Joel,

Thanks for your prompt reply, it works

Regards
Len