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

 

[rikkiw]

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

Yes, these are requests raised by different person so really includes so much weird strings! lol they just input whatever they thought in their mind....

 

[rikkiw]

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)),""))

Learnt ! thank you!