# Extract Data

#### PTori82

##### Board Regular
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!

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### Akashwani

##### Well-known Member
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
DEFGHIJK
3********
4*DataPosition last No*Results1Results2Results3*
5*1/2SM/FG3*1/241306 * *41306 * **
6*1/23*0.51/21/2*
7*2 1/23*2.52 1/22 1/2*
8*21*22 * *2 * **
9*1/2SM/NSW3*1/241306 * *41306 * **
10*1/2SM/-SHRA3*1/241306 * *41306 * **
11*2SM/+TSRA1*22 * *2 * **
12*7SM/NSW1*1010 * *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

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

#### Akashwani

##### Well-known Member
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

##### Board Regular
Ok, Wow! That worked. Thank you!

Replies
4
Views
656
Replies
0
Views
131
Replies
1
Views
573
Replies
2
Views
591
Replies
11
Views
204

1,195,631
Messages
6,010,783
Members
441,569
Latest member
PeggyLee

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back