Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 ","? -- MABeatty ------------------------------------------------------------------------ MABeatty's Profile: http://www.excelforum.com/member.php...o&userid=32258 View this thread: http://www.excelforum.com/showthread...hreadid=555007 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Grabbing the Last name
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 -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=555007 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Grabbing the Last name
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 ","? Hi, Try this: =LEFT(B17,FIND(",",B17)-1) -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Grabbing the Last name
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Grabbing the Last name
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. -- MABeatty ------------------------------------------------------------------------ MABeatty's Profile: http://www.excelforum.com/member.php...o&userid=32258 View this thread: http://www.excelforum.com/showthread...hreadid=555007 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Grabbing the Last name
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. 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Grabbing the Last name
Works like a charm, thanks -- MABeatty ------------------------------------------------------------------------ MABeatty's Profile: http://www.excelforum.com/member.php...o&userid=32258 View this thread: http://www.excelforum.com/showthread...hreadid=555007 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Grabbing Mins/Maxs Off a Chart | Charts and Charting in Excel | |||
Grabbing HTML tables into Excel | Excel Discussion (Misc queries) | |||
Grabbing recods based on date and shift values | Excel Worksheet Functions | |||
Grabbing the last Non-empty cell in a range | Excel Discussion (Misc queries) |