Extract Text from a Text String

[datastudent]

Hi,

How do I extract the word "Fixed" and "Mobile" from these list.

Australia Fixed - National
Australia Mobile - National
Belgium Fixed - National
Canada Mobile - National

 

[FormR]

Hi, here's one way..

Book2
	A	B
1	Australia Fixed - National	Fixed
2	Australia Mobile - National	Mobile
3	Belgium Fixed - National	Fixed
4	Canada Mobile - National	Mobile
Sheet1

Cell Formulas
Range	Formula
B1:B4	B1	=TEXTBEFORE(TEXTAFTER(A1," ")," ")

 

[datastudent]

Hi @FormR

Thank you for your reply.

I encounter a problem with the two words country like south korea, czech republic, puerto rico, etc.

 

[Fluff]

How about

=TEXTAFTER(TRIM(TEXTBEFORE(A1,"-"))," ",-1)

 

[FormR]

Another option (a slight tweak to my first suggestion)...

Book3
	A	B
1	Australia Fixed - National	Fixed
2	Australia Mobile - National	Mobile
3	Belgium Fixed - National	Fixed
4	Canada Mobile - National	Mobile
5	South Korea Mobile - National	Mobile
Sheet1

Cell Formulas
Range	Formula
B1:B5	B1	=TEXTBEFORE(TEXTAFTER(A1," ",-3)," ")

 

[datastudent]

How about

=TEXTAFTER(TRIM(TEXTBEFORE(A1,"-"))," ",-1)

This one works for some.

I noticed just now that I also have some data in a different format like below.

Albania - Mobile - National

 

[FormR]

Albania - Mobile - National

Same suggestion as post #5

Book3
	A	B
1	Australia Fixed - National	Fixed
2	Australia Mobile - National	Mobile
3	Belgium Fixed - National	Fixed
4	Canada Mobile - National	Mobile
5	South Korea Mobile - National	Mobile
6	Albania - Mobile - National	Mobile
Sheet1

Cell Formulas
Range	Formula
B1:B6	B1	=TEXTBEFORE(TEXTAFTER(A1," ",-3)," ")

 

[Fluff]

Or

=TEXTAFTER(TRIM(TEXTBEFORE(A1,"-",-1))," ",-1)

 

[Cubist]

If you're only expecting either "Fixed" or "Mobile", another option would be:

Book1
	A	B
1	Australia Fixed - National	Fixed
2	Australia Mobile - National	Mobile
3	Belgium Fixed - National	Fixed
4	Canada Mobile - National	Mobile
5	South Korea Mobile - National	Mobile
6	Albania - Mobile - National	Mobile
Sheet2

Cell Formulas
Range	Formula
B1:B6	B1	=IF(ISNUMBER(SEARCH("fixed",A1)),"Fixed","Mobile")

 

[kvsrinivasamurthy]

In B2 copied down.

=LET(a,SUBSTITUTE(A2," - National",""),INDEX(TEXTSPLIT(a," "),LEN(a)-LEN(SUBSTITUTE(a," ",""))+1))