Extract Data

[PTori82]

Hello,

Thanks again to those that have helped me so far! I am trying to extract data that involves fractions. Here is an example: Cell E5 reads "1/2SM/FG". The only value I want is 1/2 which would be placed in cell T5. There a few options that could be in cell E5. This cell represents visibility and the weather code that causes the surface visibility to drop (I'm a weather forecaster in the Air Force). In cell E5 you could have a fraction such as 1/2, 2 1/2, or a whole number between 1-7 (can't remember the proper names for each of these examples).

In cell E5, and in addition to the 3 types of numbers you could have, there are also a few different text variables. Here is an example: you could have "1/2SM/NSW", "1/2SM/-SHRA", "2SM/+TSRA". The list could go on forever but the bottom line is you will always have one form of the three numerical values I gave an example of above, followed by SM/ and then up to 5 characters (including a + or - designator). The only thing I want displayed in cell T5 is the numerical value. I dabbled with the Right/Left functions and that worked fine when I had a whole number, but when the number of characters in cell E5 changed, cell T5 wan't correct.

The final thing I want to do is, if cell E5 says "7SM/NSW", I want cell T5 to just display 10. The only constant is "7SM/NSW" which signifies unrestricted visibility and no significant weather. Thanks!

 

[Akashwani]

Hi,

I'm struggling to get the correct formatting of the result, so hopefully someone with a greater knowledge will jump in and sort this out!!

This is sample data and example results...

Excel Workbook
	D	E	F	G	H	I	J	K
3	*	*	*	*	*	*	*	*
4	*	Data	Position last No	*	Results1	Results2	Results3	*
5	*	1/2SM/FG	3	*	1/2	41306 * *	41306 * *	*
6	*	1/2	3	*	0.5	1/2	1/2	*
7	*	2 1/2	3	*	2.5	2 1/2	2 1/2	*
8	*	2	1	*	2	2 * *	2 * *	*
9	*	1/2SM/NSW	3	*	1/2	41306 * *	41306 * *	*
10	*	1/2SM/-SHRA	3	*	1/2	41306 * *	41306 * *	*
11	*	2SM/+TSRA	1	*	2	2 * *	2 * *	*
12	*	7SM/NSW	1	*	10	10 * *	10 * *	*
13	*	*	*	*	*	*	*	*
Sheet19

The formulas need to be copied down.

I found part of the solution here...
http://www.mrexcel.com/forum/excel-questions/447704-find-last-number-within-string.html#post2211050

I hope this helps.

Ak

 

[PTori82]

Your Results1 column is exactly what I need. How did you do that?

 

[Akashwani]

Hi,

To be honest with you, I have no idea how the formula in F5 works, it is beyond my knowledge.
The IF part of the formula is the simple bit and I didn't have to search for that answer!!

I'm pleased it works for you and thanks for the feedback.

Ak

 

[PTori82]

Ok, Wow! That worked. Thank you!