How do you delete a post?

How do you delete a post?

I know I implied that I wouldn't bother you guys any more, but I just couldn't get it out of my mind. Thanks to my DVR, I solved this one first and watched the Superbowl later.

This solution is for the last name. I arbitrarily used A16 for the work row. Basically, it looks for the 1st space and an optional 2nd space. If the 1st space is alone, it takes the rest of the name starting at 1st space+1, otherwise it takes the rest of the name starting at 2nd space +1.

=(MID(A16,(IFERROR(FIND(" ",A16,(FIND(" ",A16)+1)),FIND(" ",A16)))+1,LEN(A16)))

FWIW, I assume that the full name has been pre-processed to preclude redundant or embedded spaces, per my previous little lecture.

Now I want to solve for the Middle Name, now that you appear to have expanded it from Middle Initial. Obviously if you want to stick with a consistent single-letter middle initial without any punctuation or additional letters, the change in the code will be trivial.

Later.

Hey, I even got comfortable with the ISERROR!

To extract the middle name, work with the string in the full name starting after the 1st space, and try to deliver the number of bytes in the middle name: (position of 2nd space) - (position of 1st space) - 1. If this fails because there is no 2nd space, deliver a 0. This number becomes the third parameter of the MID function.

=MID(A16,FIND(" ",A16)+1,IFERROR((FIND(" ",A16,(FIND(" ",A16)+1))) - (FIND(" ",A16))-1,0))

To extract just the middle initial, look for a second space. If it's there, deliver the first letter of the middle name; otherwise deliver a null.

=IF(ISERROR(FIND(" ",A16,(FIND(" ",A16)+1))),"",(MID(A16,FIND(" ",A16)+1,1)))

Are we having fun yet?

Thank you BudVitoff! Love the Shakespeare I am going to go over all of this and learn it if it kills me, lol. Yes, it is advanced for my level for sure. I thought I was doing something simple and look where I ended up

The data is live, but I exported it from a CRM system we are using. In some reports it exports complete names so I couldn't sort by last name, hence the exercise of splitting out the data.

Thank you xor, Pecoflyer and BudVitoff! I learned a lot already and look forward to taking this to the next level. You are all awesome!

I'm new to this forum so I have no idea how to delete a post, sorry

Wow, this is great. I have some studying to do

In case it wasn't obvious: In spite of my longevity in programming, trying to squeeze productive logic out of a single Excel cell was not something that just rolled off my tongue. At one time or another, it was true of different languages, including COBOL, FORTRAN,PL/1, BASIC, assembly languages, and machine language (yes, I keyed programs into a CDC 160A by pressing bit buttons on the console) and others. But this little trick you pulled on us was quite the challenge for me. I worked many hours on it, and enjoyed every minute! Thank you!

While I worked on it though, it really bugged me that I was making the machine do the same thing every time I needed something, e.g., find the position of the first space, when if I had been working in VBA, I would have been able to find it once and store its value, so that the next time I needed it, it was sitting there waiting for me to grab it. That's only one example of how awkward cell work can be, in spite of the fabulous power that Excel has to get things done that I wouldn't want to do in VBA. But if you know VBA (and if you don't, it's easy to learn) there is something you should be aware of.

You can code a function in VBA to do something, say extract a middle initial, using all the efficiencies that VBA provides, give it a name like GetMI, and then simply call that function from your Excel cell like any other function: =GetMI. You can set up the function to receive parameters that make it even more powerful, like maybe giving it the number of the current row in an array of full names so that you could loop through the array to process all the names, using the same function each time, as in =GetMI(NameRow).

If I've piqued your interest, just Google "Call a VBA function from Excel" and see what shows up. Enjoy!