Extract Data

Hello all, I am tyring to extract a weather observation.  I need to get the temperature for a weather reports.  The example below is in cell A1.  I need to get the value 23 into cell c34.  Some things that are constant in a weather observation it is always preceded by the first "/" there can be mulitple "/" but the temperature is always before the first "/"  also the value can be either two digits or a "M"followed by 2 digits, like M12, which is equal to minus 12.  How can i get a way to extract this data.

KICT 092253Z 02006KT 10SM TS BKN075CB OVC110 23/15 A3001
sandramacAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
byundtConnect With a Mentor Commented:
Try the following formula:
=--SUBSTITUTE(RIGHT(LEFT(A1,FIND("/",A1)-1),3),"M","-")
0
 
sandramacAuthor Commented:
Thank it works,
0
 
byundtCommented:
With hindsight, I see that I can shorten the formula to:
=--SUBSTITUTE(MID(A1,FIND("/",A1)-3,3),"M","-")
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
Curt LindstromCommented:
Brad, one digit positive temperatures will produce a #VALUE! error with your formula

KICT 092253Z 02006KT 10SM TS BKN075CB OVC110 3/15 A3001

Curt
0
 
byundtCommented:
Curt,
I assumed temperatures between -9 and +9 would be written as M09 or 09 with a leading 0. If that should not be the case, then the following will work:
=--SUBSTITUTE(MID(SUBSTITUTE(A1," ","  "),FIND("/",SUBSTITUTE(A1," ","  "))-3,3),"M","-")
It substitutes two spaces for every one in the original text.

Brad
0
 
Curt LindstromCommented:
Brad,
I was thinking:

=IF(ISERR(--SUBSTITUTE(MID(A1,FIND("/",A1)-3,3),"M","-")),--SUBSTITUTE(MID(A1,FIND("/",A1)-2,2),"M","-"),--SUBSTITUTE(MID(A1,FIND("/",A1)-3,3),"M","-"))

..but as always you find the most efficient formula to solve a problem :-)

Cheers,
Curt

0
 
byundtCommented:
Curt,
In Excel 2007 or later, your approach might become:
=IFERROR(--SUBSTITUTE(MID(A1,FIND("/",A1)-3,3),"M","-"),--MID(A1,FIND("/",A1)-1,1))

Brad
0
All Courses

From novice to tech pro — start learning today.