The Magic Café
Username:
Password:
[ Lost Password ]
  [ Forgot Username ]
The Magic Cafe Forum Index » » Not very magical, still... » » Help with an Excel function? (0 Likes) Printer Friendly Version

George Ledo
View Profile
Magic Café Columnist
SF Bay Area
2895 Posts

Profile of George Ledo
Hi,

I need to parse out a list of names, such that I end up with the last name regardless of whether it's "Mr. So-and-so," "Mr. & Mrs. So-and-so," or any variation thereof. I've been playing with right(), search(), len(), and combinations of them, but just can't get it to work. Basically, what I need to do is count backwards (right to left) to get the position of the last space in the string -- i.e., in front of the last name -- then subtract that number from the length of the entire string, and then use the resulting number in the right() function.

The instructions in Excel Help were okay, as long as all the strings were the same type (Mr. and Mrs., or just Mr., or whatever).

Can anyone get me squared away?

Thanks much.
That's our departed buddy Burt, aka The Great Burtini, doing his famous Cups and Mice routine
www.georgefledo.net

Latest column: "Sorry about the photos in my posts here"
George Ledo
View Profile
Magic Café Columnist
SF Bay Area
2895 Posts

Profile of George Ledo
Okay, I found it online, the only version that seems to work:

=MID(A10,FIND(CHAR(1),SUBSTITUTE(A10," ",CHAR(1),
LEN(A10)-LEN(SUBSTITUTE(A10," ",""))))+1,255)

Funny, I seem to remember being able to do this in BASIC by getting the length of substrings and going from there, but I guess you can't do it in Excel unless you go into VBA.
That's our departed buddy Burt, aka The Great Burtini, doing his famous Cups and Mice routine
www.georgefledo.net

Latest column: "Sorry about the photos in my posts here"
cfrye
View Profile
Special user
Portland, Oregon, USA
940 Posts

Profile of cfrye
You could use the text to columns function to get the names, but this sort of conditional parsing is much more suited to VBA than formulas. Is a custom function an option for you?



Curt
George Ledo
View Profile
Magic Café Columnist
SF Bay Area
2895 Posts

Profile of George Ledo
Text to columns doesn't work in this case because the names are entered in so many different ways (Mr. and Mrs., Mr. and Mrs. John, John and Cathy, middle initials, and so forth) that the last names would end up in different columns.

A custom function is certainly an option, although the one I found online works really well for my purposes. Actually, I already set it up and merged the letters.

Thanks so much for your interest! Smile
That's our departed buddy Burt, aka The Great Burtini, doing his famous Cups and Mice routine
www.georgefledo.net

Latest column: "Sorry about the photos in my posts here"
cfrye
View Profile
Special user
Portland, Oregon, USA
940 Posts

Profile of cfrye
No worries -- I composed my reply while you wrote your follow-up. I certainly couldn't have created the formula you found online, so I'm glad you found a solution.



Curt
The Magic Cafe Forum Index » » Not very magical, still... » » Help with an Excel function? (0 Likes)
[ Top of Page ]
All content & postings Copyright © 2001-2021 Steve Brooks. All Rights Reserved.
This page was created in 0.18 seconds requiring 5 database queries.
The views and comments expressed on The Magic Café
are not necessarily those of The Magic Café, Steve Brooks, or Steve Brooks Magic.
> Privacy Statement <

ROTFL Billions and billions served! ROTFL