Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.

Weather Data

Latest post Tue, Feb 10 2015 4:25 PM by PeterG. 4 replies.
  • Mon, Feb 9 2015 5:47 PM

    • armycat09
    • Top 200 Contributor
    • Joined on Mon, Nov 16 2009
    • Posts 5
    • Points 89

    Weather Data

    Greetings,

    I work with amatuer weather data that I collect from web sites. I import the data into excel, but the data contains "Extra" formatting issues that have become a nusance. Temperature, humidity, wind speed, and pressure all have a symbol next to their value. I have to go into each cell and re-type it's value so that I can use the values as I intend to.

    Is there a way that I can remove the unwanted degree symbol, % (without turning it into a decimal), MPH, and in after the respective reading without retyping?

     

    Thank you,

    Chris

    • Post Points: 21
  • Mon, Feb 9 2015 10:58 PM In reply to

    • Omar
    • Top 10 Contributor
    • Joined on Sat, Jan 12 2008
    • Kitchener, ON
    • Posts 217
    • Points 2,949

    Use the Replace feature of Excel. Put your special symbol in the Find What box, and leave the Replace With box empty.

    Omar Freeman Kitchener, ON

    • Post Points: 36
  • Tue, Feb 10 2015 3:54 PM In reply to

    • armycat09
    • Top 200 Contributor
    • Joined on Mon, Nov 16 2009
    • Posts 5
    • Points 89

    The worksheet is already populated with data such as Humidity column 92%, Pressure column 29.92in...See attached worksheet.

     

    Thank You

    • Post Points: 21
  • Tue, Feb 10 2015 4:23 PM In reply to

    • armycat09
    • Top 200 Contributor
    • Joined on Mon, Nov 16 2009
    • Posts 5
    • Points 89

    Oops...I guess it would help if I clicked on replace. Thanks Omar Big Smile

    • Post Points: 5
  • Tue, Feb 10 2015 4:25 PM In reply to

    • PeterG
    • Top 10 Contributor
    • Joined on Mon, Feb 25 2008
    • Posts 224
    • Points 3,368

    Chris

    I don't know how adept you are with Excel, but you could use the following formula in cell S3

    =IF(ISNUMBER(B3),B3,LEFT(B3,FIND(" ",B3)-1)+0)

    and copy the formula across to cell AH3, and then copy the formulas down so that all your rows of data are "processed".

    The formula checks whether the cell contains a numeric value (e.g. the humidity) in which case it simply returns the numerical value (i.e. 85% becomes 0.85). Assuming that the remaining non-numeric values are of the form NumberSpaceUnit, the formula finds the position of the first space in the string, returns the number part of the string and then coerces it into a number by adding zero.

    If you want, you can copy the block of formulas and paste as values on top of your original data.

    Peter

    • Post Points: 5
Page 1 of 1 (5 items) | RSS
Copyright Excel User Group and the relevant contributors, 2010. All Trade Marks acknowledged. This site is a peer-to-peer site and NOT affiliated in any way to Microsoft Corp. All rights reserved.