MID Lookup

[Mattias1]

Hello,


I recently discovered the MID() function thanks to someone on the forum.
I am using it to decode information from a copy and pasted line to get wind direction and speed.
The line inputed is:

METAR CYKZ 081600Z 33013KT 15SM FEW060 BKN260 25/10 A2993 RMK CU1CI3

And the Formulas below work perfectly to get the wind velocity.

=MID(V26,FIND("KT",V26)-5,3)
=MID(V26,FIND("KT",V26)-2,2)

Now a problem I encountered is sometimes the copy and pasted information, the winds instead of 33013KT are replaced with 32010G17KT when winds are gusting.
Is there a way to extract the information with =MID(V26,FIND("G##KT",V26)-5,3) and =MID(V26,FIND("G##KT",V26)-2,2) first, and if that does not exist, then to use the previously mentioned formulas?

Example:
METAR CYKZ 081500Z 32010G17KT 15SM SCT260 24/10 A2994 RMK CI2 SLP136=

[NBVC]

Try:

=MID(V26,FIND("KT",V26)-IF(MID(V26,FIND("KT",V26)-3,1)="G",8,5),3)

and

=MID(V26,FIND("KT",V26)-IF(MID(V26,FIND("KT",V26)-3,1)="G",5,2),2)

also if you add +0 to the end of those, it will coerce the result into a real number, instead of a text string that MID() returns

[dilipandey]

Hi Mattias1,

Try using below formulas in both the cases:-


=IFERROR(MID(V26,FIND("KT",V26)-5,3),MID(V26,FIND("KT",V26)-8,3))

=IFERROR(MID(V26,FIND("KT",V26)-2,2),MID(V26,FIND("KT",V26)-5,2))

Regards,
DILIPandey

<click on below 'star' if this helps>

[Mattias1]

Thank you NBVC, works perfectly!


dilipandey, unfortunately this did not give the desired result, it included the term "G" in the string of text.