Extracting Data

PTori82

Board Regular
Joined
Dec 5, 2012
Messages
64
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>
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
PTori82,

The following is based on your displayed strings - that the strings ending in KT are 10 characters long.

Something like this?


Excel 2007
AB
1KCVS 131858Z AUTO 27012G17KT 10SM CLR 10/M17 A3011 RMK AO2 SLP185 T00991169 $270
2KCVS 130358Z 28023G31KT 10SM CLR 09/M15 A2992 RMK AO2A PK WND 29039/54 SLP109 T00861154 $280
Sheet1
Cell Formulas
RangeFormula
B1=MID(A1,FIND("KT",A1,1)-8,3)
 
Upvote 0
PTori82,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.
 
Upvote 0
PTori82,

How would I delete "AUTO" all together from a cell?

If this is what the cell looks like?


Excel 2007
A
1KCVS 131858Z AUTO 27012G17KT 10SM CLR 10/M17 A3011 RMK AO2 SLP185 T00991169 $
Sheet1


Click into the cell.

Then click and hold down the Alt key and then press the E key and then the F key.

In the Find what: box, type in AUTO with a trailing space character.

Click on the Replace tab.

And, then click on the Replace button.

And, this is what you will get:


Excel 2007
A
1KCVS 131858Z 27012G17KT 10SM CLR 10/M17 A3011 RMK AO2 SLP185 T00991169 $
Sheet1
 
Upvote 0
=REPLACE("A1",13,5,"") If there are always 13 characters including spaces before the AUTO word, then this should work, I am saying to count over 13 characters from the beginning and replace the next 5 characters (AUTO plus a space) and REPLACE those 5 characters with nothing, "". Adjust the 13 if needed.

hiker95, is it possible to put the two functions together (REPLACE & MID) and make them both work at the same time all the way down the column once the function is dragged down to last used cell via the fill handle? For the cells without "AUTO ", I will assume that the REPLACE Function will do nothing and only the MID Function will run?

I will assume that the OP would like another option to run the Function and keep the possibility of his original Formulas of using LEFT and RIGHT that he completely understands which he can do if he can REPLACE the unwanted AUTO portion of the string??? Just having another option is good training.
 
Upvote 0
There won't always be 13 characters. Sometimes AUTO will be displayed and sometimes it won't be displayed.
 
Upvote 0
PTori82,

I can do what you require, but, it will be with a macro, or, a UDF = User Defined Function, using VBA.

If you do not want a macro, or a function, then:

Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.
 
Upvote 0
I also have another request since you are doing a macro. Could you make the macro find "SM" and replace it with two spaces? Thanks for your help.
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top