Parse text & Numbers
 

I need to separate into columns the info below. When I use text to columns
with the space as the separator the first four columns work but after that
the columns don't line up because the text names vary in number of words. Is
there at least a way I can separate the $$'s at the end of the string into
one column?

0100106 91002 000241 19312 040930 SEMWST CD980002 280810 REVERSAL - CASH
7875.00
0100106 90762 000268 1894388966 041003 VV #908556028-00001 CD980004 280583
REVERSAL - CASH 66.02
0100106 90169 003984 107978677 040921 ASG CD980006 280664 REVERSAL - CASH
258.35
0100106 000026 000000 A-B IND CD990002 280921 A/P CHECK TOTAL 454.51

Hi
see:
http://www.dicks-blog.com/archives/2...t-2/trackback/

--
Regards
Frank Kabel
Frankfurt, Germany
"Jack" schrieb im Newsbeitrag
...
I need to separate into columns the info below. When I use text to columns
with the space as the separator the first four columns work but after that
the columns don't line up because the text names vary in number of words.
Is
there at least a way I can separate the $$'s at the end of the string into
one column?

0100106 91002 000241 19312 040930 SEMWST CD980002 280810 REVERSAL - CASH
7875.00
0100106 90762 000268 1894388966 041003 VV #908556028-00001 CD980004 280583
REVERSAL - CASH 66.02
0100106 90169 003984 107978677 040921 ASG CD980006 280664 REVERSAL - CASH
258.35
0100106 000026 000000 A-B IND CD990002 280921 A/P CHECK TOTAL 454.51

"Jack" wrote...
I need to separate into columns the info below. When I use text to columns
with the space as the separator the first four columns work but after that
the columns don't line up because the text names vary in number of words.
Is there at least a way I can separate the $$'s at the end of the string
into one column?

0100106 91002 000241 19312 040930 SEMWST CD980002 280810 REVERSAL - CASH
7875.00
0100106 90762 000268 1894388966 041003 VV #908556028-00001 CD980004 280583
REVERSAL - CASH 66.02
0100106 90169 003984 107978677 040921 ASG CD980006 280664 REVERSAL - CASH
258.35
0100106 000026 000000 A-B IND CD990002 280921 A/P CHECK TOTAL 454.51

So presumably you want to extract

7875.00
66.02
258.35
454.51

?

And Data Text to Columns, Delimited (using multiple spaces) didn't work?

Anyway, to extract these $ figures, if the first record were in cell A2, try
the array formula

=--MID(A2,LEN(A2)-MATCH(" ",MID(A2,LEN(A2)-ROW(INDIRECT("1:"
&LEN(A2))),1),0),1024)