Computers & Electronics

Need help with Excel: Using Find & Replace to identify a unique delimiter

  • Last Updated:
  • Jun 2nd, 2011 12:53 pm
[OP]
Sr. Member
User avatar
Jul 5, 2006
541 posts
1 upvote

Need help with Excel: Using Find & Replace to identify a unique delimiter

Problem

I'm exporting into Excel from a CMS and it is outputting a list of names all into one cell. I need the name in its own individual cells for each respective person. In the example below, there are three persons contained in one cell.

Example of a cell: Last, First,Last, First,Last, First

[IMG]http://cl.ly/2q0C3Z2g240b1r3F1E2G/Scree ... .48_AM.png[/IMG]

My Best Guess

It seems to be outputting the names into one cell, but there is a unique identifier between the names. The first name of the first person and the last name of the second person have a comma and no spaces between them. I've made this unique delimiter bold in the example above.

Is there a way I can use wildcards or something in Find and Replace to spot this? I've tried *,*, but the wildcards are recognizing spaces as well.

Why?


If I can find a way to use Find and Replace to spot this, I want to change the comma delimiter between persons to something more uncommon, to act as the delimiter in the Text to Columns feature so I can split each persons name into its own cell.
4 replies
Deal Fanatic
Sep 29, 2005
6426 posts
1550 upvotes
Montreal
Could you not do simply replace ",space" with / ?
Phils
[OP]
Sr. Member
User avatar
Jul 5, 2006
541 posts
1 upvote
Phils, I'm not quite sure I'm following. Forgive me for sounding like an utter noob. I've never really extensively used Excel for professional use before. Many thanks for your reply, mate

EDIT: I'm an idiot.. Solution works like a charm. I owe you a beer. :)
[OP]
Sr. Member
User avatar
Jul 5, 2006
541 posts
1 upvote
Hey all,

For all you Excel wizards out there, I have one more question:

When I do a Text to Column split, and I want data split into 3 different columns, can I mark the columns separately from another? From what I've been able to gather, the split seems to only work if I split it into 3 columns that are beside each other?

I've tried highlighting a columns, pressing CTRL, then selectively choosing others without any success.

Any help is truly appreciated! I'm nearly there! Many thanks everyone! :)
Deal Fanatic
Sep 29, 2005
6426 posts
1550 upvotes
Montreal
Glad you figured out what I was saying because reading back what I wrote it wasn't really clear.

One way your second question possibly can be done is a twist on my first suggestion. However instead of replacing ",space" with "/", replace it with "/ /" (one delimiter for each column you want to add).

Once you've changed the first delimiter to /, you can do a similar thing with the commas without a space. You would simply replace "," with ", ,".
Phils

Top