Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
extraction
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
extraction
=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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
extraction
=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) "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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extraction | Excel Worksheet Functions | |||
Word Extraction | Excel Discussion (Misc queries) | |||
data extraction | Excel Discussion (Misc queries) | |||
data extraction | New Users to Excel | |||
Data Extraction | Setting up and Configuration of Excel |