asked on

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)))

AgeOfEmpires

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

ASKER

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.

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

wchh

Try:
=VALUE(MID(A1,SEARCH(" A",A1)+2,4))

Patrick Matthews

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

sandramac

ASKER

Thanks