Extracting Numerical Values from a Text File

9 Aug 2005 3:54 PM

d_mc_alister

Hi There

At work we download text files with financial Account information in
them. Althought the format of the files are constant and the provider
of the files have provided a guideline of how to read the files, it is
becoming very tedious sifting through the text to extract the numerical
values each time. Also the number files is increasing every day. A
sample of the file would be

00000000000000000TEF502060505304202

933384121910680U190073877457586612400000260813Liam & Mary
DuSTA00001/800053803Building Soc304202
933384121910680U190111625019429612400000274935Des Ball
STA00001/800038201Building Soc304202
933384121910680U193319444876013012400000160925Brendan & Mary
CleSTA00001/800042804Building Soc304202

For Example the first 5 digits could be an Account number so I would
like to be able to extract the value 93338 from the document and insert
it into a spreadsheet or if this isnt possible even just display it in
a dialog box on the screen.

I was wondering if there was a VB Script that was capable of doing
this?

Any help would be much appreciated.

Thanks

9 Aug 2005 11:50 PM

Marty List

<d_mc_alis***@hotmail.com> wrote in message
Show quote

news:1123602848.811849.182780@g43g2000cwa.googlegroups.com...
> Hi There
>
> At work we download text files with financial Account information in
> them. Althought the format of the files are constant and the provider
> of the files have provided a guideline of how to read the files, it is
> becoming very tedious sifting through the text to extract the numerical
> values each time. Also the number files is increasing every day. A
> sample of the file would be
>
> 00000000000000000TEF502060505304202
>
> 933384121910680U190073877457586612400000260813Liam & Mary
> DuSTA00001/800053803Building Soc304202
> 933384121910680U190111625019429612400000274935Des Ball
> STA00001/800038201Building Soc304202
> 933384121910680U193319444876013012400000160925Brendan & Mary
> CleSTA00001/800042804Building Soc304202
>
> For Example the first 5 digits could be an Account number so I would
> like to be able to extract the value 93338 from the document and insert
> it into a spreadsheet or if this isnt possible even just display it in
> a dialog box on the screen.
>
> I was wondering if there was a VB Script that was capable of doing
> this?
>
> Any help would be much appreciated.
>
> Thanks

This shouldn't be too hard, but I think some of your lines got wrapped. How
many lines did you post, was it 1 line starting with "00000", followed by a
blank line, followed by 3 lines of data?

Also, how do you identify only the lines you're interested in? In other words,
how do you know which lines to ignore (like the one that starts with "00000"?

19 Aug 2005 3:29 PM

d_mc_alister

Sorry for not getting back sooner but I was away on holiday

The text was not wrapped.

I will be interested in all lines but only certain letters on each line

eg, I want to echo out the TEF50 (from the first line of textas the
Account number to a spreadsheet under a column named Account number. I
will also be looking to do something similar on all other lines. If
someone can show me the template of how to extract these values out to
a spreasheet I would be overjoyed!!

Cheers