Macro to separate string into three cells

Jleist

New Member
Joined
Nov 16, 2011
Messages
5
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!
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

vaskov17

Well-known Member
Joined
Apr 27, 2011
Messages
920
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

New Member
Joined
Nov 16, 2011
Messages
5
<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

Well-known Member
Joined
Apr 27, 2011
Messages
920
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

New Member
Joined
Nov 16, 2011
Messages
5

ADVERTISEMENT

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.
 

Jleist

New Member
Joined
Nov 16, 2011
Messages
5
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,675
Office Version
  1. 2010
Platform
  1. Windows
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))))))
 

Watch MrExcel Video

Forum statistics

Threads
1,122,216
Messages
5,594,886
Members
413,947
Latest member
gizmolucy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top