Managing bad data
 

I want to organize data into table format. the data I have on csv file
is not formated and has multiple information in single record field,
and info go on to multiple rows, for example:

NAME: abc AGE:x ADDRESS: 123

all this info is in one cell, no comma btw the info, there is space but
its not consistent, there are 1000 of those customers.Some customer info
cover multiple lines.
i want to display this data in table format having fields like
name,address, age with info stored in rows and columns.
Can somebody help!!
:confused:


--
hasn7
------------------------------------------------------------------------
hasn7's Profile: http://www.excelforum.com/member.php...o&userid=34577
View this thread: http://www.excelforum.com/showthread...hreadid=543495

Managing bad data
 

From your example it looks like your data is semicolon delimited. There is
an option to use semicolon as a delimiter in the import wizard. Try that

"hasn7" wrote:


I want to organize data into table format. the data I have on csv file
is not formated and has multiple information in single record field,
and info go on to multiple rows, for example:

NAME: abc AGE:x ADDRESS: 123

all this info is in one cell, no comma btw the info, there is space but
its not consistent, there are 1000 of those customers.Some customer info
cover multiple lines.
i want to display this data in table format having fields like
name,address, age with info stored in rows and columns.
Can somebody help!!
:confused:


--
hasn7
------------------------------------------------------------------------
hasn7's Profile: http://www.excelforum.com/member.php...o&userid=34577
View this thread: http://www.excelforum.com/showthread...hreadid=543495

Managing bad data
 

How about using Data:Text to Columns.

In the example data you listed, you could use a colon (:) as the delimeter.
Then you would just need to highlight the second column of data, and use a
Find:Replace function putting AGE in the Find box and leave the replace box
empty. Do the same for the third column using ADDRESS.

Continue for any other fields you may have.

Then you can insert a header row and type the field names above.

How does that work?



"hasn7" wrote:


I want to organize data into table format. the data I have on csv file
is not formated and has multiple information in single record field,
and info go on to multiple rows, for example:

NAME: abc AGE:x ADDRESS: 123

all this info is in one cell, no comma btw the info, there is space but
its not consistent, there are 1000 of those customers.Some customer info
cover multiple lines.
i want to display this data in table format having fields like
name,address, age with info stored in rows and columns.
Can somebody help!!
:confused:


--
hasn7
------------------------------------------------------------------------
hasn7's Profile: http://www.excelforum.com/member.php...o&userid=34577
View this thread: http://www.excelforum.com/showthread...hreadid=543495