I need to extract the first name, middle initial and last name from a cell. I am able to extract all of them when there is a middle initial, however I get an error if the name has no middle initial. Attached is a spreadsheet with my formulas.

Thank you for any help you can give me.

FORMULAS
First Name Formula =LEFT(A7,FIND(" ",A7))
Middle Initial =IFERROR(MID(A9,FIND(".",A9)-1,2),MID(A9,FIND(" ",A9)+1,2))
Last Name Formula =IFERROR(RIGHT(A7,LEN(A7)-FIND(".",A7)-1),RIGHT(A7,LEN(A7)-FIND(" ",A7)-2))

RESULTS
Full Name First Name Middle Initial Last Name
Jeffrey R. Evers Jeffrey R. Evers
Robert R Gains Robert R Gains
Deborah Harris Deborah Ha rris

* Error - Works if middle initial has a period or if middle initial has no period, doesn't work if there is no middle initial

Attached Files

Left,Mid,Right Formulas.xlsx (47.5 KB, 9 views)

You say it works if middle initial has no period, but for line 9 it did not find a period, so the error return was a null, not the "R". The non-error return was the result of the formula, whose MID value suffered from the failed FIND getting a #VALUE! error -- at which point the -1,2 part is meaningless.

If I'm right so far, then line 10 also suffers because it depends on finding a period.

Oops! I was depending on the results shown on the spreadsheet, not the ones in your post.

Now I see that the formulas you show in your spreadsheet are different from the ones in your post. Personally, I'd appreciate it if you took another look at what you've given us and make it consistent so that we can play nice.

Now regarding Deborah Harris, my guess is that there are two trailing spaces following her last name in the live data that are invisible, as spaces tend to be. Applying your code to this data would result in extracting a last name that looks like rris^^ (where ^ represents a space). This is why in all cases where leading or trailing spaces could present a problem, the data should be "trimmed"; i.e., those spaces should be removed from the data before applying any logic.

Which can be done using the TRIM function ( whether or not spaces need to be removed)

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Maybe like the attached.

Attached Files

Left-Mid-Right.xlsx (10.7 KB, 9 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, 17 views)

Yes, I apologize for that. I realized I had a mistake in the original file and resubmitted with the formulas in the post.

Thank you for your help! I'm looking at the following posts now and will try to digest it. It's a little advanced for me

Thank you! I'm going over the formulas now. Definitely advanced for my level.

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, 17 views)

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.