extraction

[Guest]

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

 

[Niek Otten]

=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


| 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

 

[Bob Phillips]

=MID(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))+1,255)&" "
&LEFT(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1,"
","")))))

and copy down

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)