Excel multiple values in one column, need to split

[redbunny78]

I have an excel spreadsheet with several values that have been stored in one field. How can I best split them into other columns?

This is what it currently looks like:

A B C
Salutation Name Designation
Dr. William R. Hendee
Rose Y. Byland MD.
Ms. Cynthia Allen

I need it to look like this:

A B C
Salutation Name Designation
Dr. William R. Hendee MD.
Dr. Rose Y. Byland MD.
Ms. Cynthia Allen

So, if it has a Dr. designated in column A - place an MD in column C. If there is an MD in column C, then place a Dr. in column A. And if there is a salutation(Ms., Mrs., Miss) in column B, place it in column A. If there isn't one, then leave it blank.

 

[SkipVought]

Hi,

What if the Dr refers to a PhD, DD or DO and not an MD?

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[redbunny78]

I asked and they said to just put the MD if there is Dr as the Salutation. They are assuming it catches most of the intances and the rest can be done manually.

 

[SkipVought]

What code do you have so far?

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[redbunny78]

None! I don't even have a clue how to go about doing this

 

[SkipVought]

If you, "don't even have a clue", why are you in the VBA forum? You might be better off in Forum68, using spreadsheet functionality. Do you have a clue about that? You could use the LEFT & RIGHT functions and IF statements to test for different text values in an adjacent column (first TIP)

You have to start somewhere. No one is going to code you a solution.

This is Tek-Tips, not Tek-we-do-your-work-for-you-for-FREE

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[mintjulep]

You write:

I have an excel spreadsheet with several values that have been stored in one field.

But then you give a sample of the existing data, that appears as if the data is in three columns.

Which is it?

You posted your question in the VBA forum, which implies that you want a programmatic solution - and the problem as stated seems to require one.

Have you considered alternates? This would be very easy if you did not constrain yourself to writing over existing data.

 

[redbunny78]

I'll look those up and see where that gets me. I am just new to this. I wasn't trying to get anyone to do everything for me. Just some clues about where to start.

Thanks!

 

[SkipVought]

This is not the place for you to start. You would be much better off in Forum68.

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}