Grabbing the Last name

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 ","?
 
S

SteveG

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
 
F

Franz Verga

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
 
V

Vincnet.

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

MABeatty

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.
 
F

Franz Verga

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
 
Top