Spliting the data

Dear guru........

i have export some data (contacts) from my mobile in excel in the following format

SURESH"""""""""""'',""""""""""9852653563""""""""""suresh@ril.com""""""""""""""
PRADEEP GOSWAMI""""""""",""""""""02836226358""""""""""""umamarine@yahoo.com

total 1000 contacts in this format now i want to split it them with suitable format
pls guide for it.
I tried for Text to column but the exported data is not in the sequence.
regds
devesh

In your first example you have 2 apostrophe's prior to the first comma, if this is a typo (ie should be "" or " as opposed to '') then I believe (based on sample) that Text to Columns will work without incident, set Delimiter to " and ensure to check "Treat Consecutive Delimiters as One".

Post a sample file if you are having problems - be sure to randomise names and remove real phone numbers...

sir,

i m unable to resolve the problem, due to data is not in sequence pls find the attachment for your help pls check and suggest your replies,

Please confirm this data is not REAL... if it is real please remove the attachment.

To answer your question... you may find running as Delimited with Comma Separator and with a Text Qualifier set to "" you get slightly better results.

thnx to all

now i replace the existing attachment to xls file of contacts
is there any other method to manage proper data

with text to column i m unable to get some of the data in proper like first name displays in last name, contact no. diplays is email id etc...
pls help

devesh.agrawal

Text to Columns has nothing to do with your problem I'm afraid. Your problem put simply, is because the data has been physically stored in the incorrect field in whichever application you have retrieved the data from, this is plainly obvious based on the delimiter positioning. To fix this you should therefore alter the source such that the data is being stored correctly in the first instance. Failing that you will need to update the post text to columns data.

I should also like to add that based on the sample file it would appear to be the case that only names are an issue so this should not take you too long to resolve... one approach (post Text to Columns run as previously advised):

First push all names into First Name Column, how ?

Highlight Column B
Edit -> GoTo -> SpecialCells -> Blanks -> OK
Enter: = then navigate to the name in D and press CTRL + ENTER

All blanks in Column B should now have been populated with the respective names in D.

Now Copy Column B and Edit -> Paste Special -> Values over B (to dispense with the inserted formulae)

You can now clear Column D altogether.

At this point you have a good starting point for splitting out the name in Column B to First / Middle / Last if needed.