CODEWITHSUNDEEP
excelexcel-formulavba
chao gao
chao gao

Reputation: 13

how to extract string based on this manner in excel?

I want to extract only these words if present in a cell:

{Beijing, New York, Japan}
I have a column with the following data(rowwise):
Nice city- Beijing, Awesome climate
Fair city- Japan, Cool weather
New York is so nice

All i want is another column which will have:
Beijing
Japan
New York

Is it possible to do it without vba?
Is there any formula? I have nth entries rowwise

Upvotes: 0

Views: 44

Answers (2)

shrivallabha.redij
shrivallabha.redij

Reputation: 5902

You can try:

=LOOKUP(2^15,SEARCH({"Beijing","New York","Japan"},A1,1),{"Beijing","New York","Japan"})

Upvotes: 2

YowE3K
YowE3K

Reputation: 23994

You could use a formula such as

=IF(IFERROR(FIND("Beijing",A1),0)=0,"","Beijing")&
 IF(IFERROR(FIND("Japan",A1),0)=0,"","Japan")&
 IF(IFERROR(FIND("New York",A1),0)=0,"","New York")

Upvotes: 0

Related Questions