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

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

--
Regards,
Tom Ogilvy


"Chuck" <libbeyc@schoollink.net> wrote in message
news:hvvmg1pmgdo27s6bp6c763a133gdb9sj39@4ax.com...
> 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

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" <libbeyc@schoollink.net> wrote in message
news:hvvmg1pmgdo27s6bp6c763a133gdb9sj39@4ax.com...
> 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

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" <bob.phillips@notheretiscali.co.uk> wrote in message
news:OTqMsNCqFHA.3544@TK2MSFTNGP15.phx.gbl...
>
> 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" <libbeyc@schoollink.net> wrote in message
> news:hvvmg1pmgdo27s6bp6c763a133gdb9sj39@4ax.com...
> > 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
>
>

On Tue, 23 Aug 2005 21:44:11 +0100, "Bob Phillips"
<bob.phillips@notheretiscali.co.uk> 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

On Tue, 23 Aug 2005 16:36:15 -0400, "Tom Ogilvy" <twogilvy@msn.com> 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

On Tue, 23 Aug 2005 21:18:41 -0400, "Tom Ogilvy" <twogilvy@msn.com> 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
--

On Tue, 23 Aug 2005 21:44:11 +0100, "Bob Phillips"
<bob.phillips@notheretiscali.co.uk> 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
--

On Tue, 23 Aug 2005 16:36:15 -0400, "Tom Ogilvy" <twogilvy@msn.com> wrote:

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

Thank you

Chuck
--