parsing data into Excel

Manish

2006-10-12 20:42:02 UTC

Kim,

Not sure if this helps you a lot but it forms a basic logic to parse your
records.

Here is how I manipulated the three lines of your text into XL and converted
them put each part of the required information into each cell.

I would suggest you follow the steps below

1. Copy past the information as is in the XL starting from A1
This way you will see all the information in column A1 to A8

For getting "John" use cell I1 and put in it =MID(A1,1,FIND(" ",A1,1))
For getting "Doe" use celll J1 and put in it =MID(A1,LEN(I1)+1,FIND("
",A1,LEN(I2)+1))

For getting "P O BOX 229" use cell K1 and put in it =A2
For getting "MT STORM" use use celll L1 and put in it
=MID(A3,1,LEN(I2)+LEN(M1))

For getting "WV" use cell M1 and put in it =MID(A3,LEN(A3)-LEN(I2)-1,2)
For getting "26739" use cell I2 and put in it =MID(A3,LEN(A3)-5,LEN(A3))
For "1.16 AC" use cell J2 and put in it
=MID(A1,LEN(A1)-LEN(I1)-1,LEN(I1)+LEN(J1))

If you are familier with the VBA you can convert it using loop and the above
logic.

Post by kimbroughton
Hi,
I have a file I have brought into Excel. It contains tens of thousands
JOHN DOE 1.16 AC
P O BOX 229
MT STORM WV 26739
To this format
JOHN DOE PO BOX 229 MT STORM WV
26739 1.16 AC
I would appreciate any help or suggestions.
Thanks