• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 385
  • Last Modified:

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 $
1 Solution
[ 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
[ 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! :)
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now