Extract word after specifying blank space

[Karnik]

Hi Expert,

In a given cell I have following sample data that runs in thousands rows but in one column

Task is to pull out any word (DXB or ABU in this case) that is 3rd from right, only if cell has 4 blank spaces in between two words or cell has 5 words in total

SYD EK DXB EK SYD
ADL EK DXB EK DEL
ISB EY ABU EY MEL




Task is to pull out 5th word (LGW or LON in this case) , only if cell has 8 blank spaces in between two words or cell has 9 words in total

PER EK DXB EK LGW EK DXB EK PER
SYD EK DXB EK LON EK DXB EK MLE

Rows runs in mix of above two

Currently I use following function to pull out specific words

=IF(COUNT(SEARCH("DXB",A1)),"DXB"," ")

Request for any function that may be able to assist based on blank spaces

Thanks
Karnik

[sandy666]

case 1:

=IF(ISERROR(SEARCH(CHAR(127),SUBSTITUTE(A10," ",CHAR(127),4))),"",MID(A10,SEARCH(CHAR(127),SUBSTITUTE(A10," ",CHAR(127),2))+1,SEARCH(CHAR(127),SUBSTITUTE(A10," ",CHAR(127),3))-SEARCH(CHAR(127),SUBSTITUTE(A10," ",CHAR(127),2))))

case 2:

=IF(ISERROR(SEARCH(CHAR(127),SUBSTITUTE(A15," ",CHAR(127),8))),"",MID(A15,SEARCH(CHAR(127),SUBSTITUTE(A15," ",CHAR(127),4))+1,SEARCH(CHAR(127),SUBSTITUTE(A15," ",CHAR(127),5))-SEARCH(CHAR(127),SUBSTITUTE(A15," ",CHAR(127),4))))

change A10 and A15 suitable.

Edit:
Formula checking spaces from left to right.
Logical test is changed to LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=4 or 8
MID(...) can be defined as NAME (e.g. ShortString and LongString) in Name Manager, so formula will be shorter and clear
Maybe someone else will give you shorter solution

formula for both cases:

=IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=4,MID(A1,SEARCH(CHAR(127),SUBSTITUTE(A1," ",CHAR(127),2))+1,SEARCH(CHAR(127),SUBSTITUTE(A1," ",CHAR(127),3))-SEARCH(CHAR(127),SUBSTITUTE(A1," ",CHAR(127),2))),IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=8,MID(A1,SEARCH(CHAR(127),SUBSTITUTE(A1," ",CHAR(127),4))+1,SEARCH(CHAR(127),SUBSTITUTE(A1," ",CHAR(127),5))-SEARCH(CHAR(127),SUBSTITUTE(A1," ",CHAR(127),4))),""))

[AlKey]

Or try this
Enter formula in B1 and copy down

=IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=4,TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",50)),50*2,50)),TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",50)),50*4,50)))

v	A	B
1	SYD EK DXB EK SYD	DXB
2	ADL EK DXB EK DEL	DXB
3	ISB EY ABU EY MEL	ABU
4	PER EK DXB EK LGW EK DXB EK PER	LGW
5	SYD EK DXB EK LON EK DXB EK MLE	LON

[sandy666]

@AlKey,
very nice formula but working with more then 8 spaces and more then 9 words.

case 2: only if cell has 8 blank spaces in between two words or cell has 9 words in total,

Task is to pull out 5th word (LGW or LON in this case) , only if cell has 8 blank spaces in between two words or cell has 9 words in total
Karnik

EDIT:
I have modified a little your formula:

=IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=4,TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)*2,LEN(A1))),IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=8,TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)*4,LEN(A1))),""))

[Karnik]

Hi Sandy666 and Alkey

Thanks for your time and expertise , solution works brilliantly
Cheers
Karnik

[Karnik]

Hi Expert,
I am back again with same issue, but different challenge

Function for cell that contains fix data characters 17 (including blank space ) given below in 1st 6 rows to pull out only last 3 characters only
Function will ignore cell that has more that 17 character cells e.g. last to rows

DATA RESULTS REQUIRED
SYD EY AUH EY BEG BEG
SYD VA SIN VA CDG CDG
SYD VA SIN VA CDG CDG
BNE TG BKK TG BNE BNE
BNE TG BKK TG BNE BNE
SYD TG BKK TG SYD SYD



BNE DL LAX DL YVR DL YYC DL SLC DL LAX DL BNE (to be ignored)
PER EY AUH EY LHE EY AUH EY PER (to be ignored)


Thanks in advance

[MarvinP]

Hey Karnik,

How about something like this in B2 and pull it down:

=If (Len(A2)>17,"",Right(A2,3))

[Karnik]

Hi MarvinP,

It works!
Thanks for simple solution, in fact I will use modify command to pull out desired results
Cheers
Karnik