Split values in cells to variables

[whitethomas12]

I have a quick question

I need help with a basic macro that will enable me to search a cell
that has lastname and first name seperated by a comma (lastname,
firstname). All I need to do is create two variables that will only
have either the first name or last name. For example

Variable1 = Last name
Variable2 = First name


Once I am able to do this then I should be able to add it to my code

Thank you for all of your help in advance

 

[Chip Pearson]

It isn't clear if you need to (1) combine existing FirstName and LastName
variables before searching for the concatenation of variable, or (2) whether
you need to split the found result into FirstName and LastName.

For (1), use code like

Dim FoundCell As Range
Dim LastName As String
Dim FirstName As String
LastName = "pearson"
FirstName = "chip"

Set FoundCell = Range("A1:A10").Find(what:=LastName & ", " & FirstName, _
LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
If FoundCell Is Nothing Then
Debug.Print "NOT FOUND"
Else
Debug.Print "FOUND AT: " & FoundCell.Address
End If


For (2), use code like:

Dim FoundCell As Range
Dim LastName As String
Dim FirstName As String
Dim VV As Variant
Set FoundCell = Range("A1:A10").Find(what:="pearson, chip", _
LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
If FoundCell Is Nothing Then
Debug.Print "NOT FOUND"
Else
VV = Split(FoundCell.Text, ",")
LastName = Trim(VV(0))
FirstName = Trim(VV(1))
Debug.Print "Last Name: " & LastName, _
"First Name: " & FirstName
End If


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Edward]

Assuming you know how to loop through cells one possibility for the code
would be
firstName = Mid(mystring, 1, InStr(mystring, ",") - 1)
lastName = Mid(mystring, InStr(mystring, ",") + 1)

 

[CjimO]

I interpreted your question as wanting to parse ("search") a cell that you
know has the "last, first" string in it.

Dim words() As String
Dim variable1 As String
Dim variable2 As String

' Replace 'ActiveCell.Value' with your cell reference
words = Split(ActiveCell.Value, ",")

' Bounds check words() to be sure you have two words
' Trim() removes surrounding white space: " Bob" -> "Bob"
variable1 = Trim(words(0))
variable2 = Trim(words(1))

 

[whitethomas12]

I interpreted your question as wanting to parse ("search") a cell that you
know has the "last, first" string in it.

Dim words() As String
Dim variable1 As String
Dim variable2 As String

' Replace 'ActiveCell.Value' with your cell reference
words = Split(ActiveCell.Value, ",")

' Bounds check words() to be sure you have two words
' Trim() removes surrounding white space:  " Bob" -> "Bob"
variable1 = Trim(words(0))
variable2 = Trim(words(1))









- Show quoted text -

Thank you. Everything worked out perfectly