Split data from name field

[dpic44]

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

[BudVitoff]

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.

[BudVitoff]

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.

[Pecoflyer]

Originally Posted by BudVitoff

. 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)

[xor]

Maybe like the attached.

[BudVitoff]

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.

[dpic44]

Originally Posted by xor

Maybe like the attached.

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

[dpic44]

Originally Posted by BudVitoff

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.

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