Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Extract Data
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
Len Len is offline
external usenet poster
  
Posts: 162
Default Extract Data
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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Extract Data
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" wrote:

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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
Len Len is offline
external usenet poster
  
Posts: 162
Default Extract Data
On Sep 27, 11:18 am, Joel wrote:
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" wrote:
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- Hide quoted text -

- Show quoted text -

Hi Joel,

Thanks for your prompt reply, it works

Regards
Len

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Data extract Brad Autry Excel Worksheet Functions 10 January 14th 10 08:46 AM
Need help in extract of data Ms. Latte Excel Discussion (Misc queries) 2 August 8th 08 05:27 AM
Tying to extract all data from a pivot pulling from external data Ted Urban Excel Discussion (Misc queries) 3 September 14th 07 10:50 AM
Extract data from jpg Don Guillett Excel Programming 2 April 26th 07 04:06 AM
How to extract the data Shiva Excel Worksheet Functions 2 November 1st 05 04:41 AM


All times are GMT +1. The time now is 01:30 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement