One VERY LONG row... break it down?

[Lisa]

I have imported general information for about 300 employees into Excel, but
it all went into just ONE row. Is there a way to break it out into the
appropriate rows? (Name, address, city, state, zip, age, gender, etc.)

Thanks!

 

[Jeffrey W. Smith]

Lisa,

You can try experimenting with the Data | Text to Column feature, using
different delimiters (Space, Tab, etc.)

HTH,

Jeff

 

[Lisa]

I've been trying that, but there are no punctuation marks (commas, tabs..)
In cell A1 is a name, A2 is address, A3 is city, A4 is state, A 5 is zip, A6
is name, A7 is address, etc.

 

[David McRitchie]

Better to fix it at the source if you can,
you really should have each employee on it's own
row, and some kind of delimiter between each field.
There is the remote possible that such delimiters are present and you
don't see them -- do you have square bullets between data items.

 

[Gord Dibben]

Lisa

Your subject line states "very long row" but it looks like you meant "very long
column".

"etc." is not too descriptive of the actual data.

Is there a consistent number of entries for each employee in the column?

i.e. 8 rows, 9 rows?

Separated by a blank row or not at each employee?

Some consistency that would allow picking and transposing the data for each
employee to one row across columns?


Gord Dibben MS Excel MVP

 

[Lisa]

No, there is NOTHING there... I went through and tried each delimiter
within Excel and none of the options did anything.

 

[David McRitchie]

Hi Lisa,

My priorities would be
1) check what you have
- change the .csv extension to .txt and look at in notepad
to see if things line up if they do then use File, import wizard
and column widths.
- see if you at least see rows of data
2) Check if there are hidden delimiters
- use a hex editor/browser to see what you have and if you can
work with it.
3) Contact the supplier and see if you can get the file
in the format that you can use with Excel.

Details:

Change the file extension from .csv to .txt and look at
it in notepad to see if columns line up, if they do then
use the file import wizard in Excel.

Checking each delimiter in the Text to Columns may
not be good enough using =CODE(MID(A1,10,1))
would tell you what the actual character is at position 10.

A hex editor would be much better
http://en.wikipedia.org/wiki/Hex_editor

Did a Google Search and most of the hex editor
sites are pay for and/or sites with McAfee advisory red
or yellow warnings.

This one was listed near the top of the first reference on the wiki
page and would be the author's site.
HxD - Freeware Hex Editor and Disk Editor | mh-nexus
http://mh-nexus.de/hxd/
and it looks to be good with loads of capabilities,
installed Beta 1.7.1 version, and looks good.