Managing bad data

[hasn7]

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!!

[SJC]

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!!
>
>
>
> --
> hasn7
> ------------------------------------------------------------------------
> hasn7's Profile: http://www.excelforum.com/member.php...o&userid=34577
> View this thread: http://www.excelforum.com/showthread...hreadid=543495
>
>

[Dominic LeVasseur]

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!!
>
>
>
> --
> hasn7
> ------------------------------------------------------------------------
> hasn7's Profile: http://www.excelforum.com/member.php...o&userid=34577
> View this thread: http://www.excelforum.com/showthread...hreadid=543495
>
>