Grabbing the Last name

I have a sheet that has names entered in a single cell in the Last, First format. How can I grab just the last name? Is there a function that allows me to return all the text up to the ","?

This will work using Last Name, First Name format but you could also use Text to Columns to split it up using the "," as the delimiter.


=LEFT(A1,FIND(",",A1,1)-1)

HTH

Steve

Nel post news:MABeatty.29v22z_1151075702.374@excelforum-nospam.com
*MABeatty* ha scritto:

> I have a sheet that has names entered in a single cell in the Last,
> First format. How can I grab just the last name? Is there a function
> that allows me to return all the text up to the ","?


Hi,

Try this:

=LEFT(B17,FIND(",",B17)-1)

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Worked like a charm, but to my surprise didn't solve my problem.

Step 2 I have several worksheets that are named by a persons last name. I wanted to take a total from each of the sheets and display them on one sheet. The second sheet simeply has "Last, First" in Row A and a total in Row B. My though was to use to refer to the last name in the formula to get the last name.

Instead of having =Smith!D20

I could have =LEFT(a1,FIND(",",a1)-1)!D20
where A1 would equal "Smith, Joe"
that way as the list changes I could simply update the name and "Fill Down" the formula.

Try this... =LEFT(H11,FIND(",",H11)-1)
(I'm not sure that I had correctly understood your problem, come back if I
was wrong)

--
KR

V.


"MABeatty" wrote:

>
> I have a sheet that has names entered in a single cell in the Last,
> First format. How can I grab just the last name? Is there a function
> that allows me to return all the text up to the ","?
>
>
> --
> MABeatty
> ------------------------------------------------------------------------
> MABeatty's Profile: http://www.excelforum.com/member.php...o&userid=32258
> View this thread: http://www.excelforum.com/showthread...hreadid=555007
>
>

Nel post news:MABeatty.29v3pc_1151077803.2689@excelforum-nospam.com
*MABeatty* ha scritto:

> Worked like a charm, but to my surprise didn't solve my problem.
>
> Step 2 I have several worksheets that are named by a persons last
> name. I wanted to take a total from each of the sheets and display
> them on one sheet. The second sheet simeply has "Last, First" in Row
> A and a total in Row B. My though was to use to refer to the last
> name in the formula to get the last name.
>
> Instead of having =Smith!D20
>
> I could have =LEFT(a1,FIND(",",a1)-1)!D20
> where A1 would equal "Smith, Joe"
> that way as the list changes I could simply update the name and "Fill
> Down" the formula.

I think yo can use the INDIRECT function in this way, if the data you want
is always in cell D20

=INDIRECT("'"&LEFT(B17,FIND(",",B17)-1)&"'!D20")


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Works like a charm, thanks