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

 

[kvsrinivasamurthy]

Dynamic formula in B2

=LET(a,SUBSTITUTE(A2:A5," - National",""), MAP(a,LAMBDA(r,INDEX(TEXTSPLIT(r,," "),LEN(r)-LEN(SUBSTITUTE(r," ",""))+1))))

 

[Fluff]

No need to use a lambda function

Fluff.xlsm
	A	B	C
1
2	Australia Fixed - National	Fixed	Fixed
3	Australia Mobile - National	Mobile	Mobile
4	Belgium Fixed - National	Fixed	Fixed
5	Canada Mobile - National	Mobile	Mobile
6	South Korea Mobile - National	Mobile	Mobile
Data

Cell Formulas
Range	Formula
B2:B6	B2	=TEXTAFTER(TRIM(TEXTBEFORE(A2:A6,"-",-1))," ",-1)
C2:C6	C2	=TEXTBEFORE(TEXTAFTER(A2:A6," ",-3)," ")
Dynamic array formulas.

 

[datastudent]

Hi,

I'm working on a new set of data now and I encounter the same problem but with different formats. I'm not sure if I can create a new forum for it so I'm just gonna ask help in here first.

The same with my first concern, I just need to identify the Fixed and Mobile. Here are some sample of the format of the data.

A1 Mobile

At&T Mobile Block A

Cable & Wireless Mobile

Clair Fixed

Fixed Local

Mobile Other

Mnt Fixed & Mobile

Mnt - Mobile

 

[Scott Huish]

How about:
=IF(ISNUMBER(FIND("Mobile",A1:A8)),"Mobile","")
=IF(ISNUMBER(FIND("Fixed",A1:A8)),"Fixed","")

 

[Fluff]

If you have the new regex functions, maybe

Fluff.xlsm
	A	B
1
2	A1 Mobile	Mobile
3	At&T Mobile Block A	Mobile
4	Cable & Wireless Mobile	Mobile
5	Clair Fixed	Fixed
6	Fixed Local	Fixed
7	Mobile Other	Mobile
8	Mnt Fixed & Mobile	Fixed, Mobile
9	Mnt - Mobile	Mobile
Sheet6

Cell Formulas
Range	Formula
B2:B9	B2	=ARRAYTOTEXT(REGEXEXTRACT(A2,"Mobile|Fixed",1))

 

[Cubist]

Try:

Book2
	A	B
1	A1 Mobile	Mobile
2	At&T Mobile Block A	Mobile
3	Cable & Wireless Mobile	Mobile
4	Clair Fixed	Fixed
5	Fixed Local	Fixed
6	Mobile Other	Mobile
7	Mnt Fixed & Mobile	Fixed, Mobile
8	Mnt - Mobile	Mobile
Sheet4

Cell Formulas
Range	Formula
B1:B8	B1	=LET(x,{"Fixed","Mobile"},TEXTJOIN(", ",,IF(ISNUMBER(SEARCH(x,A1)),x,"")))