Text to Column

  • Thread starter Thread starter Maria
  • Start date Start date
M

Maria

I dowlnload a file from a mainframe and it looks like this:

Dept Code
001234 1567 Balance Forward
9274 2567 Balance Forward from prior years

I need to parse the data so it will look like this:

001234 1567 Balance Forward
9274 2567 Balance Forward from prior years

If I use Data/Text to Column/Delimited and Space then it would break the
"1567 Balance Forward etc..." to too many fields (sometimes it can be up
to 10 fields). However, this way I can't move the break line to wherever
I want.

If I use Data/Text to Column/Fixed width then I can move the break line but
it will not parse the dept correctly (it cut 001234 to different parts).

Is there a way can I used delimited and space and can move the break line
too?

Thanks.
Maria
 
Hi Maria
try the following formulas (if your data is stored in column A)
in B1 enter
=LEFT(A1,FIND(" ",A1)-1)
in C1 enter
=MID(A1,FIND(" ",A1)+1,255)
copy both formulas down

Now select column B+C and copy them (CTRL+C). Goto 'Edit - Paste
Special' and choose 'Value' to replace the formulas with the results
 
Do the parse twice- the first time use space delimited. When the conversion is done, delete all the columns but the first 2 (with your #s) and File, save-as in Excel format

Open the mainframe file again- this time use fixed width. Position the break line before the "balance forward". After the conversion is finished, delete the first column (it's should be just the #s). Open the first file you converted above, copy those 2 columns and put them in as columns 1 & 2 in this spreadsheet. File, Save-As in Excel format. Hope that helps.
 
Back
Top