Placing a period after a middle initial

[Guest]

Hello Folks!

I have a unique situation that requires me to find and place a period in a string after the middle name. For example, in cell A2, I have the test Jonathan J McGuire. I need the text to read Jonathan J. McGuire. I think I need to start with the Find formula but after that I'm coming up short.

Thanks in advance.

Jonathan McGuire

 

[Nostrovia69]

Thanks that works awesome

 

[samfolds]

Have you tried the formula in my last post?

It worked out for a sample I made.

Keep me posted.

Samfolds

EDIT : Sorry didn't see you had written back

Glad I could help

 

[Nostrovia69]

Another question...if you don't mind

What if I wanted to pull the middle initial out all together and also check and make sure there is a middle initial to pull.

The same setup: Last name, first name m. or Last name, first name.

Can you help me with a formula for that?
I know at the beginning of this they were trying to pull the middle initial out of the center of the name not the end. Thanks,
Nostrovia69

 

[samfolds]

Just need a simple modification :

=IF(mid(A1,LEN(A1)-1,1)=" ",mid(A1,1,LEN(A1)-2),A1)

It checks wether there is a space at the end (meaning a middle name initial). If so, it returns A1 minus the 2 last characters (aka the middle name initial and the space). If there are not any middle name initial, it returns the current name.

Hope this will do the trick :D

Samfolds

EDIT : This is assuming you did not add the (.) at the end of the initial.

 

[Scott Huish]

samfolds:
I haven't tried out your formula, but if you are going to use MID starting at the first character, why not just use LEFT?

=IF(LEFT(RIGHT(A1,2))=" ",LEFT(A1,LEN(A1)-2),A1)

 

[samfolds]

samfolds:
I haven't tried out your formula, but if you are going to use MID starting at the first character, why not just use LEFT?

=IF(LEFT(RIGHT(A1,2))=" ",LEFT(A1,LEN(A1)-2),A1)

Indeed that is a good idea. But I think the first one is ok considering it is starting on the LEN(A1)-1 character... Hehe anyway "All roads lead to Rome".

Samfolds

 

[csledd]

Does anyone know the proper coding to achieve the above response?

 

[Scott Huish]

Does anyone know the proper coding to achieve the above response?

There have been a number of responses to this question. What did you need to do exactly?