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)

__________________
Using O365 v2503 - 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, 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.

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.

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

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