Need help Changing Excel Format

[jude43]

I received a spreadsheet that I need to change the format to columnar. It is currently spread across three lines for each entry.

eg.
Name JOhn Doe SSN 123-45-6789 EE Id 123 DOB 01/01/1990 ADDR 23 Any St Anytown, UT 68929 DEPT sales DOH 02/01/1998

I need each of those in a column and it is 63 pages long.

I need it like
SSN Last Name First Name DOB DOH ADDR
123-45-6789 Doe John 01/01/1990 123 Any St

Is there a way to dump this into Access and export back to excel in this format?

 

[Brianwarnock]

Don't understand the bit about spread over 3 lines, the example suggests all in one cell , in which case text to columns and a bit of reordering of the columns might do the trick.

Brian

 

[MSAccessRookie]

I created a Spreadsheet with the following Formula values and used it to do what you wanted (no access required.)

A1: (Column Width 35 - WRAP set)

Name JOhn Doe SSN 123-45-6789 EE Id 123 DOB 01/01/1990 ADDR 23 Any St Anytown, UT 68929 DEPT sales DOH 02/01/1998

B1: BLANK (For readability)

C1: FORMULA

=SEARCH("Name ",A1,1)+5

D1: FORMULA

=SEARCH(" SSN ",A1,1)+5

E1: FORMULA

=SEARCH(" EE Id ",A1,1)+7

F1: FORMULA

=SEARCH(" DOB ",A1,1)+5

G1: FORMULA

=SEARCH(" ADDR ",A1,1)+6

H1: FORMULA

=SEARCH(" DEPT ",A1,1)+6


I1: FORMULA

=SEARCH(" DOH ",A1,1)+5 { I am Now thinking about Homer Simpson }

J1: BLANK (For readability)

K1: FORMULA

=MID(A1, VALUE(C1), VALUE(D1)-VALUE(C1)-4)

L1: FORMULA

=MID(A1, VALUE(D1), VALUE(E1)-VALUE(D1)-7)

M1: FORMULA

=MID(A1, VALUE(E1), VALUE(F1)-VALUE(E1)-5)

N1: FORMULA

=MID(A1, VALUE(F1), VALUE(G1)-VALUE(F1)-6)

O1: FORMULA

=MID(A1, VALUE(G1), VALUE(H1)-VALUE(G1)-6)

P1: FORMULA

=MID(A1, VALUE(H1), VALUE(I1)-VALUE(H1)-5)

Q1: FORMULA

=MID(A1, VALUE(I1), LEN(A1)-VALUE(I1)+1)

The formulas work out properly for the sample line. As long as all other lines are similarly structured, they will continue to work.

Once the formulas have been entered, and the data has been converted, save the spreadsheet to comma delimited text. You can then import the comma delimited text back to another excel spreadsheet and ignore the columns that you do not want (B - J, and possibly A?)

 

[jude43]

Sorry, but I didn't accurately describe the format

MSAccessRookie, I appreciate your reply. I'm at fault for not describing the format well.

Col A, Row 11
Employee Name: John Doe
Col A, Row 12
Address2: 7021 ANY SCHOOL RD
Col A, Row 13
Gender: Female
Col E Row 11
Employee SSN: 123-45-6789
Col E Row 12
City/St/Zip: MT VERNON, IN 47620
Col I Row 11
Employee Number: 65001002
Col I Row 12
Hire Date: 2/26/2007
Com M Row 12
Date of Birth: 1/23/1986

The spreadsheet then skips to Row 22, and begins again with new data as depicted above.

I've attached a sample of how this data appears.

Hope there is help out there. This data extends to row 1741 !!

 

[jal]

If you have VB or VBA skills you could probably paste the whole sheet into a plain textfile (using your mouse) and then use VBA or VB to parse the textfile looking for the desire values.

 

[jude43]

Hi Jal,

No, I have no VB or VBA knowledge.

 

[MSAccessRookie]

[FONT=&quot]I Read your follow-up post and created another Spreadsheet with the following Formula values and used it to do what I believe you wanted (Still no MS Access required.)

A11: John Doe A22: Jane Doe[/FONT]
[FONT=&quot]E11: 123-45-6789 E22: 987-65-4321[/FONT]
[FONT=&quot]I11: 65001001 I22: 65001002[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]A12: 7021 Any School Road A23: 7021 Any School Road[/FONT]
[FONT=&quot]E12: Mt Vernon, IN 47620 E23: Mt Vernon, IN 47620[/FONT]
[FONT=&quot]I12: 2/26/2007 I23: 2/26/2007[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]A13: Male A24: Female[/FONT]
[FONT=&quot]E13: Sales E24: Marketing[/FONT]
[FONT=&quot]I13: 1/23/1986 I24: 12/3/1986[/FONT]
[FONT=&quot]
J1: BLANK (For readability)

K1: FORMULA: (=E11)

Result: 123-45-6789

L1: FORMULA: (=RIGHT(A11, LEN(A11)-SEARCH(" ", A11, 1)))

Result: Doe

M1: FORMULA: (=LEFT(A11, SEARCH(" ", A11, 1)-1))

Result: John

N1: FORMULA: (=I11)

Result: 65001001

O1: FORMULA: (=I13)

Result: 1/23/1986

P1: FORMULA: (=CONCATENATE(A12,", ",E12))

Result: 7021 Any School Road, Mt Vernon, IN 47620

Q1: FORMULA: (=E13)

Result: Sales

R1: FORMULA: (=I12)

Result: 2/26/07

[/FONT]
[FONT=&quot]The formulas work out properly for both of the sample lines. As long as all other lines are similarly structured, they will continue to work. Just add the formulas in the appropriate place on each starting line.

Once the formulas have been entered, and the data has been converted, save the spreadsheet to a “\” delimited text file, since “,” is a valid character in the address field. [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]You can then import the “\” delimited text back to another excel spreadsheet and ignore the columns that you do not want (A - J)[/FONT]