PDA

View Full Version : [SOLVED] Name Splitter Help



scrankin
02-08-2014, 04:38 PM
I am fairly new to VBA, and I know that there is a text to column button in Excel Data, but when I enter new names, it does not work for them. I need this program to run every time I type in a name. Also, I need it to split each name into its letter components. For example: "John James Doe" needs to be separated into "J O H N" in one column, "J A M E S" in a second column, and "D O E" into a third with each letter in a separate cell. If there is someone who could help me with this it would be greatly appreciated. Thanks!

westconn1
02-08-2014, 06:02 PM
i am not sure this is what you want, but you may be able to figure out


Private Sub Worksheet_Change(ByVal Target As Range)
If InStr(Trim(Target), " ") Then
Application.EnableEvents = False
nm = Split(Target)
For i = 0 To UBound(nm)
For j = 1 To Len(nm(i))
Target.Offset(j - 1, i).Value = Mid(nm(i), j, 1)
Next
Next
Application.EnableEvents = True
End If
End Sub

this program to run every time I type in a namethe above will run every time something is typed in, that contains a space, can not tell if it a name or not

scrankin
02-08-2014, 06:21 PM
I'm not sure I know how to use this macro. How am I supposed to run it?

Aussiebear
02-08-2014, 07:18 PM
Place it in the sheet module for the sheet you want it to run on

scrankin
02-08-2014, 07:42 PM
I have it in the module, but when I try to run it I get nothing. Does it automatically run?

westconn1
02-08-2014, 09:06 PM
Does it automatically run?yes, as long as it is in the correct module, must be the codepame for the sheet you want it to work with

scrankin
02-10-2014, 12:35 PM
yes, as long as it is in the correct module, must be the codepame for the sheet you want it to work with
Thanks man, just got it working. Does exactly what I want it to.