Extract Strings which hyphen in the code

[rikkiw]

Hello there, I have below column that I want to extract the strings with hyphen on the room code, can anyone help here? Many Thanks!

For example here, I want to add a column and export all the FM numbers from Description, such as FM1-325, FM8-525, FM2-710 ect....

Number	Description
1000000	LEAKING VALVE ON BOILERS
1000001	FM1-325 LEAK
1000002	SINK FM8-525
1000003	SINKS FM2-710 2
1000004	FM5-807 FR STERI
1000005	STUCK FM5-816 SINK
1000006	LEAKING FM2-412
1000007	FM4-606 TOILET
1000008	FM5-816-SINK
1000009	WALL FM7-508

 

[dreid1011]

Will the strings always be the same length and format (FM#-###) ? If so, perhaps this:

Book1
	A	B	C
1	Number	Description
2	1000000	LEAKING VALVE ON BOILERS
3	1000001	FM1-325 LEAK	FM1-325
4	1000002	SINK FM8-525	FM8-525
5	1000003	SINKS FM2-710 2	FM2-710
6	1000004	FM5-807 FR STERI	FM5-807
7	1000005	STUCK FM5-816 SINK	FM5-816
8	1000006	LEAKING FM-
9	1000007	FM4-606 TOILET	FM4-606
10	1000008	FM5-816-SINK	FM5-816
11	1000009	WALL FM7-508	FM7-508
Sheet1

Cell Formulas
Range	Formula
C2:C11	C2	=IFERROR(MID(B2,SEARCH("FM?-",B2),7),"")

 

[rikkiw]

Hello Dreid, thank you so much for replying.

Unfortunate there is not only FMX-XXX code, there is also TX-XXX, TX-XXXX ect.... Just now I also noted that there are also code like FMXXXX. So looks like no formal pattern, but I think extracting FMX-XXX, TX-TTT should be sufficient...

 

[dreid1011]

Perhaps this then until something better comes along:

Book1
	A	B	C
1	Number	Description
2	1000000	LEAKING VALVE ON BOILERS
3	1000001	FM1-325 LEAK	FM1-325
4	1000002	SINK T8-525	T8-525
5	1000003	SINKS FM2-710 2	FM2-710
6	1000004	FM5-807 FR STERI	FM5-807
7	1000005	STUCK FM5-816 SINK	FM5-816
8	1000006	LEAKING FM-
9	1000007	FM4-606 TOILET	FM4-606
10	1000008	FM5-816-SINK	FM5-816
11	1000009	WALL FM7-508	FM7-508
Sheet1

Cell Formulas
Range	Formula
C2:C11	C2	=IFERROR(MID(B2,SEARCH("FM?-",B2),7),"")&IFERROR(MID(B2,SEARCH("T?-",B2),6),"")

 

[rikkiw]

You're genius Dreid! It helps a lot, thank you and wish you a good day!

 

[dreid1011]

Thank you, and you're welcome. I am still going to play with it to see if I can get something a little bit more inclusive.

 

[dreid1011]

Okay, this should be a little bit more inclusive. I can't guarantee you won't get some undesired results occasionally, but worth a shot:

Book1
	A	B	C
1	Number	Description
2	1000000	LEAKING VALVE ON BOILERS
3	1000001	FM1-325 LEAK	FM1-325
4	1000002	SINK T8-5254	T8-5254
5	1000003	SINKS FM2710 2	FM2710
6	1000004	FM5-807 FR STERI	FM5-807
7	1000005	STUCK FM5-816 SINK	FM5-816
8	1000006	LEAKING T5-444	T5-444
9	1000007	FM4-606 TOILET	FM4-606
10	1000008	FM5-816-SINK	FM5-816
11	1000009	WALL FM7-508	FM7-508
Sheet1

Cell Formulas
Range	Formula
C2:C11	C2	=TEXTBEFORE(SUBSTITUTE(SUBSTITUTE(TEXTAFTER(SUBSTITUTE(B2,"FM","@FM"),"@",,,,"")&" "," ","@"),"-","@",2),"@",1)&IFERROR(TRIM(MID(B2,SEARCH("T?-",B2),7)),"")

 

[Scott Huish]

This is the only item that doesn't match the formats of the other ones. Is there supposed to be a - hyphen before SINK?

FM5-816-SINK

 

[Scott Huish]

If so,
=TRIM(LEFT(SUBSTITUTE(LET(t,TEXTSPLIT(B2&" "," "),FILTER(t,ISNUMBER(SEARCH("-",t)),"")),"-",REPT(" ",255),2),255))

If not,
=LET(t,TEXTSPLIT(B2&" "," "),FILTER(t,ISNUMBER(SEARCH("-",t)),""))

 

[rikkiw]

Okay, this should be a little bit more inclusive. I can't guarantee you won't get some undesired results occasionally, but worth a shot:

Book1
	A	B	C
1	Number	Description
2	1000000	LEAKING VALVE ON BOILERS
3	1000001	FM1-325 LEAK	FM1-325
4	1000002	SINK T8-5254	T8-5254
5	1000003	SINKS FM2710 2	FM2710
6	1000004	FM5-807 FR STERI	FM5-807
7	1000005	STUCK FM5-816 SINK	FM5-816
8	1000006	LEAKING T5-444	T5-444
9	1000007	FM4-606 TOILET	FM4-606
10	1000008	FM5-816-SINK	FM5-816
11	1000009	WALL FM7-508	FM7-508
Sheet1

Cell Formulas
Range	Formula
C2:C11	C2	=TEXTBEFORE(SUBSTITUTE(SUBSTITUTE(TEXTAFTER(SUBSTITUTE(B2,"FM","@FM"),"@",,,,"")&" "," ","@"),"-","@",2),"@",1)&IFERROR(TRIM(MID(B2,SEARCH("T?-",B2),7)),"")

Tried this one and it did work much better than the first one, capturing more values now! Much appreciated