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

jesuis_danny

Junior Member
Jun 2, 2011
6
0
0
Hey all, I've stumbled onto a problem and it's bothered me for days. Hoping for an Excel guru to offer some advice! I may not be looking at this in the most effective or efficient manner..

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

Screen_shot_2011-06-02_at_10.04.48_AM.png


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.
 
Last edited:

kranky

Elite Member
Oct 9, 1999
21,014
137
106
Welcome to AnandTech!

If you don't mind a multi-step process, this will work.

First, do find & replace
Find what: ~,<space>
(just to clarify, that's a tilde, a comma, and a space character)
Replace with: !

Now you'll have an ! between first and last names.
Next, use Text to Columns with comma as the delimiter.

Then go back and replace the ! with whatever you want.
 

jesuis_danny

Junior Member
Jun 2, 2011
6
0
0
@kranky

Just wanted to say a big thank you. :) If only you were in London / Brighton, UK right now, I'd call ya down to a pub and we'd cheers to that with a few pints!
 

jesuis_danny

Junior Member
Jun 2, 2011
6
0
0
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! :)
 

kranky

Elite Member
Oct 9, 1999
21,014
137
106
Can't do that. You get to choose ONE cell where it will begin, then it uses the columns to the right of that.