How would i extract certain information from a spreadsheet ?

Hi All,

I am in need of a way to 'scan' information in a spreadsheet and extract certain text patterns and the like, and then reformat into desired specs ...

Is this a relativley easy or hard thing to do, given that i am quite inexperienced with complex VB ...

Thanks for your help guys n girls, much appreciated!!

Paul

Welcome to the Forums.

What type of text patterns? Are you wanting/needing to do this from VB 6?

the text patterns vary ... heres the situation:

I get differing invoices from different clients, they are always in the same format (from each relevant client).

What i need to do is extract specific info like:

- Invoice #
- Description of goods
- Country of origin
etc

So its 99% AplhaNumeric extraction.

I would ideally like to keep it all in excel, as this will allow for further use and development with others in the office (that are not software literate) ...however Excel will alllow a bit of futreproofing for the masses ;)

Thanks for your reply mate and words of welcome :D

Hear from you soon

Thanks again

Paul

Well to parse things like an Invoice # and other stuff its best if its in a constant position/cell. If its not then we will need to write something to search for some identifier that will tell us where the starting point is.

Can you make a mockup of how the sheets look?

because i know exactly what i need from each different supplier and i know that the search criteris (alphanumeric string) never changes for each...what i need is some form of recursive statement that 'scans' a document (say pasted into a spreadhseet) and extract info based upon a 'found' item and then extract based upon each 'find'.

By using variables in the recursive search i could use the one generic search function to accomodate every invoice by passing in the specific details of the relevant supplier etc ...

I hope this is making sense...

Yes, this is what I am after. Finding the identifying maker that signifies where (what cell) to get the info from.

There is a .Find method of the range object but is the "Invoice #" always the same in all and is the needed data in the next cell below or to the right? This is why a small textual representation of what we are looking at will be helpful. ;)

this is a mock up:

Code:

---

Sales Invoice No.  : CIH/102-04
                  Vessel / Voyage No. : MV.BALTREUM TREDER V.444
                  Departure          : JAKARTA
ETD : 27/04/2006
                  Arrival            : SYDNEY
ETA : 18/05/2006
                  Your Reference      : 31367, 31382, 30765, 30712,
31462, 32046, SAMPLE ALBASIA
                  Our Reference No.  : 2826-18, 2827-18, 2884-15,
2884-16, 2884-17, 2885-6, 2885-7, 2885-8, 2885-9
                                        2885-10, 2885-11, 2893-3, 2893-4, 2955-2, 2955-3, 2977-1
                  Invoice Print No.  : 7336, 7337, 7338, 7339, 7340,
7341, 7342, 7343, 7344, 7345, 7351, 7346, 7347
                                        7348, 7349, 7350
                  Description of Goods:
                  Note / Instructions : LOADING DATE : 25/04/06, PEB NO
: 192296



                  MIX MERANTI FINGER JOINT, MIX MERANTI E2E FOR DOOR KOMPONEN,MERANTI DOOR JAMB PROFILE, MERANTI
                  DECORATIVE FRAME, MERANTIDECORATIVE MOULDING, MERANTI DOORS, MERBAU DOORS

              ------------------------------------------------------------------------
-------------------------------
                  Product          Height  Width    Thick
Volume    Unit    U/Price          Total Price
                  Code      Design    (mm)    (mm)    (mm)  Quantity
(cbm)  of Sale    AUD    Discount    FOB AUD
 
------------------------------------------------------------------------
-------------------------------
                  DSSC      1        2052      29      28    1260.0
2.099    EA        2.01    0.00    2,532.60

                  DSSC      1        2450      29      28    1188.0
2.363    EA        2.40    0.00    2,851.20

                  OTHC      SSF      770      29      28    1260.0
0.788                                    0.00

                  SSF        1        570      29      28    4064.0
1.881    EA        0.30    0.00    1,219.20

                  SSF        1        670      29      28    17820.0
9.695    EA        0.35    0.00    6,237.00

 
CONTINUED ON PAGE  2

---

this is an example of the file ....

Thanks a bunch for your help mate!!!!!!!!!!!!!!!!

yeah, i looked at the .find attribute in excel as a function, however, it seems like such a 'brute force' way to do it ....

but hey, im open to all suggestions :)

"Sales Invoice No. :"
"ETD :"
"ETA :"
etc. are the identifiers. We will need to perform a search using each one as the rows may differ on the number of rows inbetween each identifier.

Is the colon ":" actual data or is it the representation of a vertical line or cell limit?

its all actual data exactly as it is received (for this supplier) ...even the same unformatted mess :p

I understand. I made a sample of an Excel Workbook. Is the data formatted correctly? Can you correct and fill in a bit more?

yup ...thats looking good...now the other stuff that needed :)

- product
- unit
- unit price
- volume
- total price

for each line :)

hey mate, im finishing work now ....hopefully ill check this post tonight when i get home - thanks again!

Something like this will find the "Invoice No." and read the cell next to it and set sheet2 cell A1 with its value. This is the technique you will need to use for each of your search items needed.

VB Code:

Workbooks(1).Sheets(1).Activate
Workbooks(1).Sheets(1).Cells.Find(What:="Sales Invoice No.", LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
 
Workbooks(1).Sheets(2).Cells(1, 1).Value = Workbooks(1).Sheets(1).Cells(ActiveCell.Row, ActiveCell.Column + 1).Value2