0

I have an existing formula that work super great at stripping literal crap from a report we get and gives me the name. I am wondering if I can take this to the next level and now take the last name off the result of that formula and then place it in the front. IE, using my name you would get Gerald W Maxwell as a result. And I'd like to get Maxwell, Gerald W (not punctuation).

Here's a name example from the report, and the formula as well:

Name Example:  Chat Operator Performance by Operator by Day for 1234567_1234567890_Gerald W_Maxwell

Formula:  =IFERROR(IF(RIGHT($B1,6)="EXEMPT",LEFT(SUBSTITUTE(RIGHT($B1,LEN($B1)-68),"_"," "),LEN(SUBSTITUTE(RIGHT($B1,LEN($B1)-68),"_"," "))-7),SUBSTITUTE(RIGHT($B1,LEN($B1)-68),"_"," ")),"DELETE")

So, if you place that formula in cell A1 and the name example you will get Gerald W Maxwell. This is great, I wanna know if we can put the last name first based on that.

Before I post this, let me break down what the formula is doing first. At it's heart is RIGHT($B1,LEN($B1)-68) and this strips the raw name from from the fluff. This is then wrapped with a substitute function to get rid of any underscores from the name. The next wrapper is an If statement looking for the word "EXEMPT" (because the people that create this report thought it would be a nice feature...), and lastly, complete the wrapping with a bow... an IFERROR statement that gives you a nice "DELETE". This is so I can run a macro to delete the rows of stuff I don't need from the data table.

What is giving me the most grief is that I have people with a first and last name, some have a first, middle and last, and a few special individuals with first, middle, surname and last name.

If it can't be done, I can code this to do text to columns and re-concatenate on the other side of that, but if I can get it into a single line formula, I can save myself the hassle of the coding.

Cœur
  • 37,241
  • 25
  • 195
  • 267
xXCableDogXx
  • 11
  • 4
  • It's hard to help you with only 1 sample row, it would be nice to see different cases with desired output next to it. – BrakNicku Mar 12 '15 at 19:47
  • are there always 78 characters before the name? – Ron Rosenfeld Mar 12 '15 at 20:13
  • yes there is always 78 characters before the start of the name. – xXCableDogXx Mar 12 '15 at 21:22
  • You also need to give examples of what the data looks like for the other varieties of names you mention. – Ron Rosenfeld Mar 13 '15 at 00:36
  • Sorry for the late comment, I've been out on vacation. I didn't add any other examples because they are literally the difference between 2 words, 3 words, 4 words and in one rare instance, 5 words in the names. I should also point out that the example of my name is the format, there is no underscore between any of the first/given/surnames, the only underscore will come at the last name. So my name is Gerald W_Maxwell, where as your name would be Ron_Rosenfeld. – xXCableDogXx Mar 17 '15 at 17:09
  • If you had two middle names it would be Ron First Second_Rosenfeld, and so on. Other than that everything before the name remains constant – xXCableDogXx Mar 17 '15 at 17:09

1 Answers1

1

This answer is based on a formula devised to get the right-most character in a string in this answer:

=right(A1,len(A1) - FIND("@",SUBSTITUTE(A1," ","@",(LEN(A1)-LEN(SUBSTITUTE(A1," ","")))/LEN(" "))))&" "&left(A1,FIND("@",SUBSTITUTE(A1," ","@",(LEN(A1)-LEN(SUBSTITUTE(A1," ","")))/LEN(" "))))

If your name is in A1, plunk this in A2 and you will get Maxwell Gerald W. This does not deal with exceptions like Jr. or with multiple surnames. It just chops off the last bit after the final blank space and reattaches it to the front of the string.

Community
  • 1
  • 1
Dan Oswalt
  • 2,201
  • 2
  • 14
  • 24
  • This would be pretty impractical if you tried to do it all in one formula, plugging several instances of your complex formula above to get the string it is parsing. Personally, I think maintaining a sheet full of formulas like this would be much more of a hassle to maintain than the coding that would be required to make custom functions for both of these formulas (sorry, I don't know VBA, but this would be simple for someone who does). – Dan Oswalt Mar 12 '15 at 21:01
  • yeah, I thought as much, my testing gave me about the same results. So I guess unless anyone else has anything to weigh in on this, we can call this one busted. – xXCableDogXx Mar 12 '15 at 21:23
  • busted because it doesn't deal with exceptions, or because it doesn't do it all in one formula? – Dan Oswalt Mar 12 '15 at 22:08
  • Because it doesn't do it all in one formula. Sorry for the late comment, I've been away on vacation. Yeah the idea was to have it all in one formula instead of coding VBA to text to columns and then re-concantenate on the other side of that. But that's exactly what I wound up doing anyhow. – xXCableDogXx Mar 17 '15 at 17:04