Grabbing the Last name

  • Thread starter Thread starter MABeatty
  • Start date Start date
M

MABeatty

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 *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 ","?
Click to expand...


Hi,

Try this:

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

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy
 
Try this... =LEFT(H11,FIND(",",H11)-1)
(I'm not sure that I had correctly understood your problem, come back if I
was wrong)
 
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.
 
Nel post *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.
Click to expand...

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
 
Back
Top