dividing a cell
 

HandEdie

The above name is in one cell with no separator. How do I split a major
list of this type. The names are in one column

Thanks

dividing a cell
 

You need VBA for this. Here is a UDF that works
Open VBA, add a module and cpy this into module
On worksheet:
Suppose the first name is in A1, Select B1:C1 enter =Splitter(A1) and commit
with CTRL+SHIFT+ENTER
Copy the two cells down to last name
When done use Copy and Paste Special to convert formulas to text

Need help with VBA?
David McRitchie's site on "getting started" with VBA
http://www.mvps.org/dmcritchie/excel/getstarted.htm



Function splitter(onename As String)
Dim temp(2)
myLen = Len(onename)
mySwitch = 1
temp(0) = Mid(onename, 1, 1)
For j = 2 To myLen
myChar = Mid(onename, j, 1)
If mySwitch = 1 Then
If Asc(myChar) 90 Then
temp(0) = temp(0) + myChar
Else
mySwitch = 2
temp(1) = temp(1) + myChar
End If
Else
temp(1) = temp(1) + Mid(onename, j, 1)
End If
MsgBox myChar & mySwitch
Next j
splitter = temp
End Function

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Dendy" wrote in message
...
HandEdie

The above name is in one cell with no separator. How do I split a major
list of this type. The names are in one column

Thanks

dividing a cell
 

Remove the Msgbox line of code!
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Dendy" wrote in message
...
HandEdie

The above name is in one cell with no separator. How do I split a major
list of this type. The names are in one column

Thanks

dividing a cell
 

over my head!

"Bernard Liengme" wrote:

You need VBA for this. Here is a UDF that works
Open VBA, add a module and cpy this into module
On worksheet:
Suppose the first name is in A1, Select B1:C1 enter =Splitter(A1) and commit
with CTRL+SHIFT+ENTER
Copy the two cells down to last name
When done use Copy and Paste Special to convert formulas to text

Need help with VBA?
David McRitchie's site on "getting started" with VBA
http://www.mvps.org/dmcritchie/excel/getstarted.htm



Function splitter(onename As String)
Dim temp(2)
myLen = Len(onename)
mySwitch = 1
temp(0) = Mid(onename, 1, 1)
For j = 2 To myLen
myChar = Mid(onename, j, 1)
If mySwitch = 1 Then
If Asc(myChar) 90 Then
temp(0) = temp(0) + myChar
Else
mySwitch = 2
temp(1) = temp(1) + myChar
End If
Else
temp(1) = temp(1) + Mid(onename, j, 1)
End If
MsgBox myChar & mySwitch
Next j
splitter = temp
End Function

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Dendy" wrote in message
...
HandEdie

The above name is in one cell with no separator. How do I split a major
list of this type. The names are in one column

Thanks

dividing a cell
 

where do I find the MSGBOX?

"Bernard Liengme" wrote:

Remove the Msgbox line of code!
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Dendy" wrote in message
...
HandEdie

The above name is in one cell with no separator. How do I split a major
list of this type. The names are in one column

Thanks