Extracting Data

[PTori82]

Good afternoon,

I would really like some help on how to extract data from weather observations. Here are two different formats:

KCVS 131858Z AUTO 27012G17KT 10SM CLR 10/M17 A3011 RMK AO2 SLP185 T00991169 $ KCVS 130358Z 28023G31KT 10SM CLR 09/M15 A2992 RMK AO2A PK WND 29039/54 SLP109 T00861154 $ What I'm trying to do is extract the first 3 numbers from any value that ends with "KT". This was working fine by using the LEFT and RIGHT formulas until I ran into the "AUTO" designator. With weather observations you are going to either have an Auto remark or no auto remark. How can I extract the data in either scenario? I'm not too good using the MID formula for some reason and I suspect that may be how to get this done. Thanks! </SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP> </SPAN>

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>

</SPAN>

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>

 

[hiker95]

PTori82,

So far we dot not know what cells, rows, columns, your raw data is in, or, where the results will be.

1. I will need to see more samples of your long strings.

2. I will need to see the results your are looking for.


What version of Excel and Windows are you using?


Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker

Or, when using Internet Explorer, just put borders around your data in Excel and copy those cells into your post.
See reply #2 the BLUE text in the following link:
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045


If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.