Forum Discussion
Text to column for contact information - perplexed
A coworker entered each contacts data into one cell, wrapping the text so each data point is on its own line.
First Name Last Name
Job Title
Phone Number
Is there any way to separate this contact information into 5 columns: First name, last name, job title, phone number and email address. Can this be done a faster more efficient way than using text to columnfeature? There are over 100 contacts like this.
1 Reply
lbrosten1995 "Over a 100 contacts" isn't very much. You should be able to replace the linefeeds by commas and then use text-to-columns with the comma as the delimiter. The whole process will perhaps take less than a minute. Using Find & Replace, find Ctrl+J (i.e. linefeed) and replace with , (comma).
If you happen to be on a Mac (like me right now), Ctrl+J will not work for you. Then use SUBSTITUTE to replace the line-feeds (i.e. CHAR(10) with a comma. For example, =SUBSTITUTE(A1,CHAR(10),",") and copy down. Copy the entire column and paste as values to another column to get rid of the formulas. Now use text-to-columns with the comma as the delimiter. Now, the whole process will perhaps take a minute longer.
