# Extract Data

Hello all, I am tyring to extract a weather observation.  I need to get the pressure for a weather reports.  The example below is in cell A1.  I need to get the value 3001 into cell c34.  Some things that are constant in a weather observation it is always follows the letter A and then four digits   How can i get a way to extract this data.

KICA 092253Z 02006KT 10SM TS BKN075CB OVC110 23/15 A3001
AgeOfEmpires

Assuming we're working with data in cell A1, this should work for you for a single cell calc:

=IF(LEFT(RIGHT(MID(A1,1,SEARCH("/",A1)-1),3),1)="M",-VALUE(RIGHT(MID(A1,1,SEARCH("/",A1)-1),2)),VALUE(RIGHT(MID(A1,1,SEARCH("/",A1)-1),3)))

I think you modified the question after I read it and was formulating my solution.

I provided a solution to your question that asked how to extract the temperature, when it precedes the first "/" and may be optionally preceded by an "M" for minus.  Now I see the question has changed to extracting pressure.

This one is a little tricker since we don't know how many "A"'s can be in the string prior to the pressure.

If I develop a solution, I'll respond back.

sandramac

The only constant is the "A" will immediately follow the four digits, any other A in the cell will not have just four digits after it.
Patrick Matthews

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Try:
=VALUE(MID(A1,SEARCH(" A",A1)+2,4))
wchh,

Yes, but suppose you have:

KICA 092253Z 02006KT 10SM TS BKN075CB OVC110 23/15 A33339 A3001

Your formula would grab "3333" and not "3001".  Also, suppose we had:

A3001 KICA 092253Z 02006KT 10SM TS BKN075CB OVC110 23/15

Your formula won't pick it up if the string is at the start of input.

:)

Patrick