Formula Revision

Hello, I have a formula that extracts data from a line.  The formula is =MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+4,2). The data format has changed a bit.  See below

KDDD 091200Z 12010KT, current formual extracts 12
KDDD 091200Z 12010G18KT, need to extract 10
KDDD 091200Z COR 12010KT, need it to take in account and extra space and extract 10
KDDD 091200Z COR 12010G18KT, need to extract 10

sandramacAsked:
Who is Participating?
 
gbanikConnect With a Mentor Commented:
=MID(A1,FIND("|",SUBSTITUTE(A1," ","|",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+4,2)
0
 
barry houdiniCommented:
Is the 12 constant? If so try

=MID(A1,FIND("12",A1)+3,2)

regards, barry
0
 
gbanikCommented:
Sample file for above code
Get10.xlsx
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

 
barry houdiniCommented:
.......mmm, yes, my suggestion won't work, will it, even if 12 is constant because 12 can (and does) occur earlier in the string, so disregard my suggestion above.

Here's another possibility which identifies the text after the last space (a little like gbanik's suggestion)

=MID(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)),4,2)

regards, barry
0
 
sandramacAuthor Commented:
The 12 is not constant that value will change
0
 
gbanikCommented:
Have u tried my suggestion?
0
 
sandramacAuthor Commented:
Hello in the formula =MID(A1,FIND("|",SUBSTITUTE(A1," ","|",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+4,2) There will also be text after it for example KDDD 091200Z COR 12010G18KT 7SM BKN100,
0
 
barry houdiniConnect With a Mentor Commented:
Both my suggestion and gbanik's will return the 4th and 5th characters after the last space.....so in tht example the result will still be "10".....but I assume that's co-incidental.

In order to provide a formula that works in all cases it's normally necessary to identify some rule or consistency - I can only guess what that might be unless you can provide a larger sample.

From the examples so far it looks like you always want either the 4th and 5th characters of the 3rd "word"....unless that "word" is not numeric (i.e. "COR" in your examples) in which case you need the 4th and 5th characters of the 4th "Word". Will that work for you? If so try this formula based on gbanik's suggestion

=MID(A1,FIND("|",SUBSTITUTE(A1," ","|",2+ISERR(MID(A1,FIND("|",SUBSTITUTE(A1," ","|",2))+1,1)+0)))+4,2)

regards, barry

0
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.

All Courses

From novice to tech pro — start learning today.