Formula Help

Hello All

I have a formula that parses out data here is the code, listed below.  It works fine, until today.  I get an error value when this set of data appears, listed under the formula.  I am trying to parse out the time, which is normally looks for the first Z, however in the data, the first Z is in the station identifier, i need to look for the second Z and then parse out the number.  How do i modified this code to handle these instances.  Thanks

=IF(MID('Parsed Data'!A1,FIND("Z",'Parsed Data'!A1)-4,2)="23",0,MID('Parsed Data'!A1,FIND("Z",'Parsed Data'!A1)-4,2))+2

KBZN 291656Z 22022G32KT 10SM FEW090 SCT120 18/00 A2964 RMK AO2 PK WND 23032/1655 SLP014 T01780000 $
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:

Here is one method:

=IF(MID('Parsed Data'!A1,FIND("Z",MID('Parsed Data'!A1,FIND(" ",'Parsed Data'!A1)+1,255))+FIND(" ",'Parsed Data'!A1)-4,2)="23",0,MID('Parsed Data'!A1,FIND("Z",MID('Parsed Data'!A1,FIND(" ",'Parsed Data'!A1)+1,255))+FIND(" ",'Parsed Data'!A1)-4,2))+2

I suspect other "Experts" may offer differing proposals.

I have attached a workbook demonstrating the existing formula, & my revision.


barry houdiniCommented:
Is the "station identifier" always 4 characters? If so then you can just start looking for "Z" at character 5.......also you don't really need the IF if you use MOD, i.e.

=MOD(MID('Parsed Data'!A1,FIND("Z",'Parsed Data'!A1,5)-4,2)+0,23)+2

If the station identifier is of variable length try

=MOD(MID('Parsed Data'!A1,FIND("Z",'Parsed Data'!A1,FIND(" ",'Parsed Data'!A1))-4,2)+0,23)+2

regards, barry

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
[ fanpages ]IT Services ConsultantCommented:
Just for clarity, whenever I say other "Experts" may propose a different formula, I always mean barry will offer a shorter version! :)
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Steven HarrisPresidentCommented:
Just for clarity, whenever I say other "Experts" may propose a different formula, I always mean barry will offer a shorter version! :)

+1 :)
[ fanpages ]IT Services ConsultantCommented:
You're welcome, sandramac.
sandramacAuthor Commented:
Sorry, thank you both for you help with my problem.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.