Parsing String

[Blackhorse]

I have to parse a string, C:\WINDOWS\Help\AGT0405.HLP, in a query and split
the FileName. I have written expressions so that "C:\WINDOWS\Help\ show in
the folder path column. I also wrote an expression to put the FileName into
a column, AGT0406.HLP. But I need to also split the FileName down to a
Prefix Column being the alphabetic portion, AGT and a suffix column that
would show the 0405 (numeric). Is there an expression that would break the
file down lower? These are the expressions that were written:

Path: Left([Files],InStrRev([Files],"\"))

FileName: Mid([Files],InStrRev([Files],"\")+1,15) (These work.)
Thanks

 

[John W. Vinson]

I have to parse a string, C:\WINDOWS\Help\AGT0405.HLP, in a query and split
the FileName. I have written expressions so that "C:\WINDOWS\Help\ show in
the folder path column. I also wrote an expression to put the FileName into
a column, AGT0406.HLP. But I need to also split the FileName down to a
Prefix Column being the alphabetic portion, AGT and a suffix column that
would show the 0405 (numeric). Is there an expression that would break the
file down lower? These are the expressions that were written:

Path: Left([Files],InStrRev([Files],"\"))

FileName: Mid([Files],InStrRev([Files],"\")+1,15) (These work.)
Thanks

Is the prefix of predictable length? or might one file be AGT0406, another
K3114, and a third WHISPER0512?

John W. Vinson [MVP]

 

[Blackhorse]

The length is not predictable. I may have one that is 400.htm and since it
is numeric it should go in the suffix column. So all alpha characters go in
the prefix and all numeric characters go in the suffix column.

I have to parse a string, C:\WINDOWS\Help\AGT0405.HLP, in a query and split
the FileName. I have written expressions so that "C:\WINDOWS\Help\ show in
the folder path column. I also wrote an expression to put the FileName into
a column, AGT0406.HLP. But I need to also split the FileName down to a
Prefix Column being the alphabetic portion, AGT and a suffix column that
would show the 0405 (numeric). Is there an expression that would break the
file down lower? These are the expressions that were written:

Path: Left([Files],InStrRev([Files],"\"))

FileName: Mid([Files],InStrRev([Files],"\")+1,15) (These work.)
Thanks

Is the prefix of predictable length? or might one file be AGT0406, another
K3114, and a third WHISPER0512?

John W. Vinson [MVP]

 

[John W. Vinson]

The length is not predictable. I may have one that is 400.htm and since it
is numeric it should go in the suffix column. So all alpha characters go in
the prefix and all numeric characters go in the suffix column.

That will take some VBA code. You'ld need to step byte by byte through the
string looking for the first numeric digit, and use the Val() function on the
rest of the string.

I'm tied up today or I'd write something quick. It would need some pretty
careful error checking to handle all the possible combinations - what would
you do with (say) ABC13E, or A2BC1000?


John W. Vinson [MVP]

 

[Blackhorse]

Thank you....the set up for the file is, for example, AGT0465 or 7632 they
are not intermingled. They could be all alpha characters or numeric
characters but if they are combined the numeric characters come after the
alpha characters. I am digging deep to find some information on it. Thanks
again