Google Sheet | Extract multiple values from one string

Question

I am trying to extract all the strings end with "KT" and put them into one cell, however it only show the first result (24010KT) Preferred result: 24010KT,VRB05KT,21008KT,26008KT,VRB04KT

I used below formula, =REGEXEXTRACT(A1,"\w+KT")

String in A1 // TAF 110500Z 1106/1212 24010KT 9999 FEW010 SCT030 TX33/1106Z TX32/1206Z TN28/1122Z TEMPO 1106/1112 4000 SHRA FEW010CB SCT020 TEMPO GR 1109/1115 VRB05KT TEMPO 1120/1202 4000 SHRA FEW010CB SCT020= SS METAR 110630Z 21008KT 190V250 9999 FEW018 32/24 Q1007 NOSIG=
METAR 110530Z 26008KT 240V300 9999 2800N SHRA FEW018 31/26 Q1008 NOSIG= SS METAR 110430Z VRB04KT 9999 FEW018 SCT028 32/25 Q1008 NOSIG= //

JPV · Accepted Answer · 2021-08-11 11:25:47Z

1

See if this helps

=join(", ", filter(split(A1, " "), regexmatch(split(A1, " "), ".*KT|MPS")))

edited Aug 11, 2021 at 11:25

answered Aug 11, 2021 at 11:09

JPV

27.4k4 gold badges37 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Franco Over a year ago

This is what I want. Thanks a lot. But if I also want to extract "*MPS" OR "*KT", how should I amend the formula?

JPV Over a year ago

Glad to hear that worked. Since you are new here, you may want to check out this link.

JPV Over a year ago

I updated the formula so that it should work for strings ending with 'MPS' or 'KT'. Hope that helps?

Franco Over a year ago

Thank you. That's really help!!

Collectives™ on Stack Overflow

Google Sheet | Extract multiple values from one string

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related