Macro to separate string into three cells

[Jleist]

Is there any way to create a macro for the following:

I have the following format

First Name MI Last Name in Column A.

Sometimes there is a space on the last name for someone with a last name Mc Cormick (for example)

Also, sometimes the list of names is short and sometimes the list of names is long. Is there a way to get a "universal" macro in place, that will not depend on the number of names in the list but will be able to detect when the list ends?

So far I was able to run the macro to get them from text-to-column into cells A, B, and C. However, the middle column sometimes contains the last name if there isn't a middle initial. I need a way to test the middle column for more than one character. If there is more than one character, it is a last name and needs to be in the third column.
Thank you so much for your help!

 

[vaskov17]

Paste this formula starting in B2 and drag down. It should create a comma delimited version of the name, which you can use the TextToColumns feature on:

=IF(LEN(A5)-LEN(SUBSTITUTE(A5," ",""))=1,SUBSTITUTE(A5," ",",,"),IF(FIND(CHAR(32),A5,FIND(CHAR(32),A5,1)+1)-FIND(CHAR(32),A5,1)=2,CONCATENATE(LEFT(A5,FIND(CHAR(32),A5,1)-1),",",MID(A5,FIND(CHAR(32),A5,1)+1,1),",",RIGHT(A5,LEN(A5)-FIND(CHAR(32),A5,FIND(CHAR(32),A5,1)+1))),CONCATENATE(LEFT(A5,FIND(CHAR(32),A5,1)-1),",,",RIGHT(A5,LEN(A5)-FIND(CHAR(32),A5,1)))))

 

[Jleist]

<P>Perfect! Any way to put that in a macro - the reason I ask is I want to be able to put the macro on several work stations for other staff to use if they have to separate the same type of information over three cells. </P>
<P> </P>
<P>Also, when I went to separate the data the first time (text to column) it separated the formula. I think it was because of the way the cell was formated before putting the forumla in it. I copy and pasted the data the formula gave me as "value only". Then did text to column and it worked perfectly. </P>
<P> </P>
<P>I just am not sure how to get it into a macro since it requires dragging the formula down to the end of a list that might not be the same for each user of the macro... Any ideas? I am going to keep playing around. This is definitely a head start! </P>

 

[vaskov17]

Just realized the formula I gave should be pasted in B5 not B2 since it references A5.

It might be easier to create a macro instead of attempting to convert the formula to vba.

 

[Jleist]

I think I got it to work well enough. However, how can I get a macro on other work stations? I saved it on the server but when I import the macro it has to be saved to a spreadsheet so I can't use it on multiple documents.

 

[Rick Rothstein]

However, how can I get a macro on other work stations? I saved it on the server but when I import the macro it has to be saved to a spreadsheet so I can't use it on multiple documents.

Maybe make it into an Add-In?

http://www.fontstuff.com/vba/vbatut03.htm

 

[Jleist]

I was able to get it as an Add-in, tested it and was able to put it on other workstations, and as a Macro - perfect! What can I take onto the end of that formula to get it to return a blank cell instead of #VALUE! if there is nothing in the cell?

THANKS SO MUCH!

 

[Rick Rothstein]

What can I take onto the end of that formula to get it to return a blank cell instead of #VALUE! if there is nothing in the cell?

You would simply wrap it with an IF test to see if the cell was blank or not...

=IF(A5="","",IF(LEN(A5)-LEN(SUBSTITUTE(A5," ",""))=1,SUBSTITUTE(A5," ",",,"),IF(FIND(CHAR(32),A5,FIND(CHAR(32),A5,1)+1)-FIND(CHAR(32),A5,1)=2,CONCATENATE(LEFT(A5,FIND(CHAR(32),A5,1)-1),",",MID(A5,FIND(CHAR(32),A5,1)+1,1),",",RIGHT(A5,LEN(A5)-FIND(CHAR(32),A5,FIND(CHAR(32),A5,1)+1))),CONCATENATE(LEFT(A5,FIND(CHAR(32),A5,1)-1),",,",RIGHT(A5,LEN(A5)-FIND(CHAR(32),A5,1))))))