Maybe like the attached.

Attached Files

Left-Mid-Right.xlsx (10.7 KB, 11 views)

XOR: Yeah, that would be much better. There's only one anomaly left: What's the significance of lines 18-20? One can't infer that they are the result of executing the formulas shown, because the result in line 10 shows the error.

PECOFLYER: I used the word "trimmed" intentionally, because I was aware of the TRIM function; however, when Excel Help appeared to discuss TRIM only in terms of editing movies, I decided against mentioning the function. Oddly enough, I did learn something new though. TRIM not only removes leading and trailing spaces, but it also compresses spaces between words; i.e., it removes redundant spaces.

You're never too old to learn something new.

ButVidoff

Sorry, but I am not sure what you mean.

Would this be better?

Attached Files

Left-Mid-Right_2.xlsx (10.8 KB, 19 views)

Thank you!

Hi BudVitoff, Pecoflyer and xor!

In the middle initial, I see that if the first FIND causes an error then the result is null, if no error, the formula then looks at the two MID's. The +1 gets rid of the space before and the -1 gets rid of the space after, correct? And I see the -1 in the formula for the first name, which gets rid of the space between the first name and middle initial.

For the last name, I thought a RIGHT function was needed. Finding the length makes sense to me, but I'm not grasping the use of the two MID"s.

Can you explain it using if then else?

FIRST NAME:
=LEFT(A18,FIND(" ",A18)-1)

MIDDLE INITIAL:
=IF(ISERROR(FIND(" ",A18,1+FIND(" ",A18))),"",MID(A18,FIND(" ",A18)+1,FIND(" ",A18,1+FIND(" ",A18))-FIND(" ",A18)-1))

LAST NAME:
=IF(ISERR(FIND(" ",A18,1+FIND(" ",A18))),MID(A18,FIND(" ",A18)+1,LEN(A18)),MID(A18,1+FIND(" ",A18,1+FIND(" ",A18)),LEN(A18))

I broke down the formula for the last name and got the results below, which work if there is a middle initial. If there is no middle initial I get an error with all three. So I know all three parts work together, just not understanding the syntax.

Jeffrey R. Evers --> =(FIND(" ",A22,1+FIND(" ",A22))) -->result is 11, counts back from last name
Jeffrey R. Evers --> =MID(A23,FIND(" ",A23)+1,LEN(A23)) -->result is R. Evers
Jeffrey R. Evers --> =MID(A21,1+FIND(" ",A21,1+FIND(" ",A21)),LEN(A21)) -->result is Evers

Thank you again! I really appreciate your help.

See explanation in the attached.

Attached Files

Left-Mid-Right_3.xlsx (12.1 KB, 19 views)

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!

My bad. My concern was that you had highlighted this stuff without any sort of comment describing its significance. I was too thick-headed at that time of morning to look at the formulas "underneath" the highlighted stuff to see what you had changed. I'll study it later to find out more. but I just wanted to get this element of the thread wrapped up right away.

You can just ignore my comment, and I'll ignore how you spelled my name.

DPIC44: Definitely advanced for your level, eh? Well by the time we've got this thing wrapped up tight, your level will have been kicked up a notch! Let me slip into lecture mode on Data Quality for a moment.

Data Quality: To assume or not to assume (with apologies to Shakespeare).
We got involved with this thing without knowing anything about what activity preceded the current problem. Much of the logic we're applying depends on the data not having any extra spaces lying around. The three test-data names don't seem to have any problem (although I'm sure I'm right about Ms. Harris messing things up)*, so we make assumptions that allow us to take certain shortcuts. The fact is, test data isn't live data. This is really a Systems Analysis (SA) concern, not a programming concern. A good SA will get the data verified for quality as early in the process as possible, so that every program that follows can legitimately take shortcuts that depend on that quality, saving a lot of programming, coding, and cpu time. Would you trust the data's quality if it were the creation of keyboard input? So keep that in mind for the current problem. One single TRIM statement will guarantee that there are no multiple spaces within the name and no leading or trailing spaces; and by the way, your test data should include some bad stuff just to make sure it gets handled properly. You can throw out the TRIM statement later if your SA tells you that data quality is guaranteed.

Thread discipline: I screwed up right at the start because the spreadsheet formulas differed from those in the post and I was studying the spreadsheet version. I later got an apology for this. Now we've got a slew of spreadsheets with different titles, each one presumably an improvement with Left-Mid-Right_3-1.xlsx representing a helluvalot of work, but I'm wondering what I missed in Left-Mid-Right_3, and why the switch in numbering? I'm not sure which spreadsheet I should be looking at.

(Time passes)

I think I've finally realized that you're working with the highlighted area that deals with the ISERROR function. I'm too tired to study it now, but at first glance I'd prefer to stay with the IFERROR technique. It's simple and compact and works, once we pre-trim to handle the Harris-type problem. Admittedly, its mechanism is a bit weird, but once you get the feel of it, it does the job quite nicely. I like it.

Oh m'Gawd, color me red. I just found out that ...3-1, and ...3-2, etc are being caused by my own downloading after not being able to get Excel to show me ...3. I'm going to bed now. I'll look at it tomorrow.

*I WAS sure, but see the next post!

Yeah I assumed she had trailing spaces. (You know what they say about assuming.) There are no trailing spaces, because LEN(A10) = 14 and not the 16 I expected. Surprise, there's a bug in the program!

Now frankly, I think you guys are better at what I'm proposing because I'm not used to squeezing a whole bunch of logic into a single cell, as opposed to VBA, where you have all the room in the world. I suspect that you've already concluded the following, but just in case you haven't:

First name works fine. Now, if there's more than one space in the full name, then Initial and Last work fine, else Initlal is blank and Last starts with the byte following the blank found.

And I don't really care if the guy is President. If the form says "Middle Initial", then just put in one character!

I gotta change my profile. I'm more of a Mac guy now, and "Novice" makes me a bit uncomfortable, since I started programming in 1957 -- but that doesn't mean that I don't make mistakes.

It's been fun. I'm going to go watch football.

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.

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!