separating data from within a cell

[JayDawg]

I have a database in which the name format is ex.
Smith,John,James in one column. I would like to separate
the name into three separate columns so each part of the
name has it's own colomn. Is there a way to convert it
without having to change each one manually? I would
greatly appreciate any assistance in this matter.

V\R

JayDawg

 

[Chris O]

Select the column, then go "Data" > "Text to Columns..." and follow the
menus

Chris O
____________________________________________________

 

[Wouter]

Hi JayDawg,

One way to deal with this is using an unser defined function:

Public Function SplitNameFirstSecond(ByVal combined As String) As
Variant
Dim intFirst As Integer
Dim intSecond As Integer
Dim strNames(3) As String
'
intFirst = InStr(combined, ",")
intSecond = InStr(intFirst + 1, combined, ",")
If intFirst > 0 Then
strNames(0) = Left(combined, intFirst - 1)
If intSecond > 0 Then
strNames(1) = Mid$(combined, intFirst + 1, intSecond -
intFirst - 1)
strNames(2) = Mid$(combined, intSecond + 1)
Else
strNames(1) = Mid$(combined, intFirst + 1)
strNames(2) = ""
End If
Else
strNames(0) = combined
strNames(1) = ""
strNames(2) = ""
End If
SplitNameFirstSecond = strNames
End Function

In your workbook select the three cells where you want the names from
the first record and enter =SplitNameFirstSecond(CombinedNames), where
CombinedNames refers to the cell with the three names. Hold Shift +
Control and hit the Enter key. Copy Down for the rest of your
database.


Good Luck

Wouter HM.