Macro to find a comma in a string
 

A1 contains LastName, FirstName
If B1 = mid(A1,1,find(",",A1)-1) then B1 will be = to the LastName.
How can I write a macro that will returen the LastName to whatever the
ActiveCell is?

Chuck L

Macro to find a comma in a string
 

ActiveCell.Value = Mid(Range("A1"),1,Instr(Range("A1"),",")-1)

--
Regards,
Tom Ogilvy


"Chuck" wrote in message
...
A1 contains LastName, FirstName
If B1 = mid(A1,1,find(",",A1)-1) then B1 will be = to the LastName.
How can I write a macro that will returen the LastName to whatever the
ActiveCell is?

Chuck L

Macro to find a comma in a string
 

iPos = Instr(1,Range("A1").Value,",")
If iPos 0 Then
Activecell.Value = Right(Range("A1").Value, _
Len(Range("A1").Value) - iPos)
End If

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Chuck" wrote in message
...
A1 contains LastName, FirstName
If B1 = mid(A1,1,find(",",A1)-1) then B1 will be = to the LastName.
How can I write a macro that will returen the LastName to whatever the
ActiveCell is?

Chuck L

Macro to find a comma in a string
 

Think you would need LEFT for lastname.

Sub AA()
iPos = InStr(1, Range("A1").Value, ",")
If iPos 0 Then
ActiveCell.Value = Left(Range("A1").Value, _
iPos - 1)
End If

End Sub

--
Regards,
Tom Ogilvy

"Bob Phillips" wrote in message
...

iPos = Instr(1,Range("A1").Value,",")
If iPos 0 Then
Activecell.Value = Right(Range("A1").Value, _
Len(Range("A1").Value) - iPos)
End If

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Chuck" wrote in message
...
A1 contains LastName, FirstName
If B1 = mid(A1,1,find(",",A1)-1) then B1 will be = to the LastName.
How can I write a macro that will returen the LastName to whatever the
ActiveCell is?

Chuck L

Macro to find a comma in a string
 

On Tue, 23 Aug 2005 21:44:11 +0100, "Bob Phillips"
wrote:


iPos = Instr(1,Range("A1").Value,",")
If iPos 0 Then
Activecell.Value = Right(Range("A1").Value, _
Len(Range("A1").Value) - iPos)
End If

Slightly different approach. However, *Range* is the magic word.
Thank you.

Chuck

Macro to find a comma in a string
 

On Tue, 23 Aug 2005 21:18:41 -0400, "Tom Ogilvy" wrote:

Think you would need LEFT for lastname.

Sub AA()
iPos = InStr(1, Range("A1").Value, ",")
If iPos 0 Then
ActiveCell.Value = Left(Range("A1").Value, _
iPos - 1)
End If

End Sub

I have a number of cell I'm working with and all but this one require the *Mid*
function. I just got carried away with *Mid*. However, I am going to change
this one cell to use *Left*. It is just a cleaner function for this case.

Thank you.

Chuck
--

Macro to find a comma in a string
 

On Tue, 23 Aug 2005 16:36:15 -0400, "Tom Ogilvy" wrote:

ActiveCell.Value = Mid(Range("A1"),1,Instr(Range("A1"),",")-1)

*Range* is the magic word. I had the ActiveCell.Value part.
Thank you.

Chiuck

Macro to find a comma in a string
 

On Tue, 23 Aug 2005 21:44:11 +0100, "Bob Phillips"
wrote:


iPos = Instr(1,Range("A1").Value,",")
If iPos 0 Then
Activecell.Value = Right(Range("A1").Value, _
Len(Range("A1").Value) - iPos)
End If

A slightly different approach. Hwever, *Range* is still the magic word.

Thank you.

Chuck
--

Macro to find a comma in a string
 

On Tue, 23 Aug 2005 16:36:15 -0400, "Tom Ogilvy" wrote:

ActiveCell.Value = Mid(Range("A1"),1,Instr(Range("A1"),",")-1)
*Range* is the magic word.

Thank you

Chuck
--