Last name first name formula? For middle intial

[zone709]

Hi I use this formula as of now. It works good with first name last name first.
It doesn't work good with if a name has a middle initial. Can something be added to this formula to change that the right way also.

=IF(B4="","",IF(ISNUMBER(SEARCH(" ",B4))=TRUE,RIGHT(B4,LEN(B4)-FIND(" ",B4))& " " & LEFT(B4,FIND(" ",B4)-1),B4))

Example person with middle intial

Jose J Aruga
J Aruga Jose <--- returns this

Aruga J Jose <--- need it to return this when it sees a middle initial. possible?

 

[UniMord]

Here is one way to do it:

=IFERROR(MID(B4, SEARCH("|", SUBSTITUTE(B4, " ", "|", LEN(B4)-LEN(SUBSTITUTE(B4, " ", ""))))+1, 2^8) & IFERROR(MID(B4, SEARCH(" ? ", B4), 3), " ") & LEFT(B4, FIND(" ", B4)), B4)

 

[UniMord]

My answer above, gives you an extra space at the end. To fix that, use this:

=IFERROR(MID(B4, SEARCH("|", SUBSTITUTE(B4, " ", "|", LEN(B4)-LEN(SUBSTITUTE(B4, " ", ""))))+1, 2^8) & IFERROR(MID(B4, SEARCH(" ? ", B4), 3), " ") & LEFT(B4, FIND(" ", B4)-1), B4)

 

[zone709]

ok this works greats. Thanks very much. Thanks for the reply cheers

 

[zone709]

Hi one small thing I seen. Anyway to add if finds nothing blank cell then return empty cell. Right now if it finds nothing it returns 0. If not its fine I can easier make a code to clear cells if 0. Just wanted to see first before I did that thanks

 

[UniMord]

Wrap the formula in T(), that'll get rid of the 0.

 

[zone709]

Hi thanks for the reply. So I never wrapped formula before. I and looking it up now on how, but I'm not getting far. Maybe I did before and I'm just confusing myself I just don't know. Can you post an example on what I need to do with the T(). thanks if you can

 

[UniMord]

By "wrap" I just meant, drop the entire formula into T(). Since T() only displays text values, not numbers, the 0 disappears.
Here's how:
=T(IFERROR(MID(B4, SEARCH("|", SUBSTITUTE(B4, " ", "|", LEN(B4)-LEN(SUBSTITUTE(B4, " ", ""))))+1, 2^8) & IFERROR(MID(B4, SEARCH(" ? ", B4), 3), " ") & LEFT(B4, FIND(" ", B4)-1), B4))

 

[zone709]

ok I think I misunderstood. It works thanks again. I rather add this then adding small code to remove 0 in B. thanks