We help IT Professionals succeed at work.

Formula Revision

sandramac
sandramac asked
on
597 Views
Last Modified: 2012-05-10
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

Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2013

Commented:
Is the 12 constant? If so try

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

regards, barry
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Commented:
Sample file for above code
Get10.xlsx
CERTIFIED EXPERT
Most Valuable Expert 2013

Commented:
.......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

Author

Commented:
The 12 is not constant that value will change

Commented:
Have u tried my suggestion?

Author

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,
CERTIFIED EXPERT
Most Valuable Expert 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.