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

[jesuis_danny]

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

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.

 

[kranky]

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.

 

[GrumpyMan]

Yeah Text to Columns feature is the way you want to go with this.

 

[jesuis_danny]

@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]

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]

Can't do that. You get to choose ONE cell where it will begin, then it uses the columns to the right of that.