=RIGHT(A1,LEN(A1)-FIND("^",SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))&"
"&LEFT(A1,FIND("^",SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))

Watch out for line wraps, they don't belong in the formula

To see what's happening:

Col B:
=LEN(A1)-LEN(SUBSTITUTE(A1," ","")) This counts the number of spaces
Col C:
=SUBSTITUTE(A1," ","^",B1) This replaces the last space with a ^
Col D:
=FIND("^",C1) This finds the position of the ^
Col E:
=RIGHT(A1,LEN(A1)-D1) This extracts the bit after the last space
Col F:
=E1&" "&LEFT(A1,D1) This adds a space and the initials

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"oldLearner57" wrote in message
...
| hi community
|
| i have these data :
|
| A B C
|
| 1 A. Yamamoto
|
| 2 Q. C. Carver
|
| 3 O. Sanchex
|
| 4 F. X. Layman
|
| how can i extract and have it as in B column as:
|
| A B C
|
|
| 1 A. Yamamoto Yamamoto A.
|
| 2 Q. C. Carver Carver Q. C.
|
| 3 O. Sanchex Sanchex O.
|
| 4 F. X. Layman Layman F. X.
|
| thanks community for the assistance
|
| :)
|
|
| --
| oldLearner57