Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Extract Data

Posted on 2011-09-09
7
Medium Priority
?
316 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:sandramac
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 81

Accepted Solution

by:
byundt earned 2000 total points
ID: 36514404
Try the following formula:
=--SUBSTITUTE(RIGHT(LEFT(A1,FIND("/",A1)-1),3),"M","-")
0
 

Author Closing Comment

by:sandramac
ID: 36514418
Thank it works,
0
 
LVL 81

Expert Comment

by:byundt
ID: 36514435
With hindsight, I see that I can shorten the formula to:
=--SUBSTITUTE(MID(A1,FIND("/",A1)-3,3),"M","-")
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 18

Expert Comment

by:Curt Lindstrom
ID: 36514478
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
 
LVL 81

Expert Comment

by:byundt
ID: 36514610
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
 
LVL 18

Expert Comment

by:Curt Lindstrom
ID: 36514657
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
 
LVL 81

Expert Comment

by:byundt
ID: 36514733
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

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question