Scrub file part 2

[goss]

Hi ng
Using xl 2003

Apologies for breaking the thread, but have not seen any follow-ups t
my reply.

Appreciate the recommendations for possibilities other than xl, bu
would like to make this work in xl if possible

I am dumping invoice details from Progress database to text file.
Open text file in xl
Using Find and Mid to attempt to "parse" needed data into structure
format.
Text to columns yields sporadic results

I can extract everything except amount of invoice.
Problem is truncation and variable length of amount portion of string.

Max = 9,999,999.99
Truncation: If whole dollar output is formatted as 99 no trailin
zeroes.

My formulas:
FIND:
=FIND("0 ",A1)

MID:
=MID(A1,B1+2,6)

Results: (Mixed success)
106.81
130 "V
28 ""

Sample data:
158002 1 0 106.81 "Nyarko Williams" "CONTRACT # 1079--Event Date
06/3/2004--Acct# RAVAADCEUH" ? "158002" 0 "" "" 70043000 0 no 0 "Y"
"" 0 no no 0 ? 0 "" "" "" "" ""

158003 1 0 123.48 "Nelda Stoudenmi" "Contract # 10650--Event Dat
06/3/2004" ? "158003" 0 "" "" 70043000 0 no 0 "N" 0 "" 0 no no 0 ? 0 "
"" "" "" ""

158005 1 0 28 "" "Contract # 1062--Event Date: 05/27/2004" ? "158005"
"" "" 70043000 0 no 0 "N" 0 "" 0 no no 0 ? 0 "" "" "" "" ""

Drop me an e-mail if you would like the entire source file
Remove nospam.

(e-mail address removed)

 

[AlfD]

Hi!

I'm trying to work out the problem.

I copied your 3 lines of sample data into a worksheet (A1:A3)
I applied Text to Columns using <Space> as delimiter.

I got what seemed to me to be a very acceptable separation of the dat
right across to column X.
In particular, column D has

106.81
123.48
28

Is this sample typical? Am I missing something?

I didn't see your earlier post so maybe I'm not up to speed.

Al

 

[goss]

Thanks Alf

Obviously I'm a moron.
I was doing some of this from memory
I was able to extract many items into columns using
Text >> Columns

All that is left to search and parse:

Contract # 1006--Event Date: 06/1/2004 FAS # 264430

Easy!

Obviously I've forgotten more about xl than I ever learned.
Thanks agai