Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
"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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert text to numbers | Excel Discussion (Misc queries) | |||
Sorting when some numbers have a text suffix | Excel Discussion (Misc queries) | |||
I enter numbers and they are stored as text | Excel Discussion (Misc queries) | |||
How to convert Numbers to text | Excel Worksheet Functions | |||
How to Replace numbers and text with numbers only? | Excel Worksheet Functions |