+ Reply to Thread
Results 1 to 11 of 11

How to exclude special characters like °

  1. 12-05-2013, 10:33 AM #1
    barc0d3guy
    barc0d3guy is offline
    Registered User
    Join Date
    10-23-2013
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    30

    How to exclude special characters like °

    Hi, there. I am work on modelling various mechanics of a game on excel. I am taking real time data from here. After importing the data, I tried accessing the data, from another sheet. I am trying to access these values in another sheet for a formula to calculate the temperature at a given altitude. However, the temperature data in the "temperature data" sheet is in °'s and when i try to access them in the other sheet, "temperature", i get an error "A value used in the formula is of wrong data type". When i try taking the values in ° from the "temperature data" sheet in cell i64 (named as "temp") on the "temperature" sheet in the cell B29, i get the above mentioned error in the cell where i am suppose to get the final temperature, E29 in my case.

    I can't remove the ° from the "Temperature Data" sheet, because each time i refresh the sheet, i will get the values with °. Is there a workaround for this??
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 12-05-2013, 11:24 AM #2
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: How to exclude special characters like °

    Try this

    =SUBSTITUTE(B29,CHAR(176),"")-9.8*((D29/1000)-(C29/1000))

    This will allow you to calculate without errors

    But would be even better to clean up data at the source.
    Last edited by AlKey; 12-05-2013 at 11:27 AM.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne
    Reply With QuoteRegister To Reply
  3. 12-05-2013, 11:40 AM #3
    barc0d3guy
    barc0d3guy is offline
    Registered User
    Join Date
    10-23-2013
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: How to exclude special characters like °

    i was trying this
    But no luck. Or may be I didn't do it right
    Reply With QuoteRegister To Reply
  4. 12-05-2013, 11:44 AM #4
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: How to exclude special characters like °

    Just copy and paste formula in cell E29 on your 'temperature' sheet of the workbook that you posted.
    Reply With QuoteRegister To Reply
  5. 12-05-2013, 11:47 AM #5
    amit.wilson
    amit.wilson is offline
    Forum Contributor amit.wilson's Avatar
    Join Date
    07-09-2013
    Location
    Gotham
    MS-Off Ver
    Excel for Mac 2011
    Posts
    283

    Re: How to exclude special characters like °

    CHAR(176) doesn't work on OSX. Here's an alternative if you like: replace B29 with LEFT(B29,LEN(B29)-1).

    Cheers
    <-- If you're happy & you know it...click the star.:-)
    Reply With QuoteRegister To Reply
  6. 12-05-2013, 11:50 AM #6
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: How to exclude special characters like °

    Quote Originally Posted by amit.wilson View Post
    CHAR(176) doesn't work on OSX. Here's an alternative if you like: replace B29 with LEFT(B29,LEN(B29)-1).

    Cheers
    I just did it worked just fine!
    Reply With QuoteRegister To Reply
  7. 12-05-2013, 11:53 AM #7
    amit.wilson
    amit.wilson is offline
    Forum Contributor amit.wilson's Avatar
    Join Date
    07-09-2013
    Location
    Gotham
    MS-Off Ver
    Excel for Mac 2011
    Posts
    283

    Re: How to exclude special characters like °

    @AlKey

    CHAR(176) gives me the infinity symbol: ∞.

    Character map problems you think?
    Reply With QuoteRegister To Reply
  8. 12-05-2013, 12:09 PM #8
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: How to exclude special characters like °

    Quote Originally Posted by amit.wilson View Post
    @AlKey

    CHAR(176) gives me the infinity symbol: ∞.

    Character map problems you think?

    here is the ASCII Character Codes link:

    http://www.danshort.com/ASCIImap/

    unless it is different in the location other then USA
    Last edited by AlKey; 12-05-2013 at 12:11 PM.
    Reply With QuoteRegister To Reply
  9. 12-05-2013, 12:18 PM #9
    barc0d3guy
    barc0d3guy is offline
    Registered User
    Join Date
    10-23-2013
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: How to exclude special characters like °

    I just did what Akey mentioned in his first post and it worked. I was having difficulties in getting a mid value from the sheet "Temperature data" cell G88. I just wanted the number "3593" from the string "Altitude 3593 m." in G88, I tried this and it worked for me =MID(' temperature data'!G88,11,LEN(' temperature data'!G88)-13). Hope this helps someone who is trying to get mid string values.
    Just a heads up, i tired clearing the ° from the data source in sheet Temperature data by using =LEFT(i64, LEN(i64)-1) in the sheet "Temperature data" cell "I64", the value returned was 0°c any idea why it returned that value?
    Last edited by barc0d3guy; 12-05-2013 at 12:20 PM.
    Reply With QuoteRegister To Reply
  10. 12-05-2013, 12:25 PM #10
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: How to exclude special characters like °

    This is because some cells have ° attached to the number and some appear there by formatting (like in cell E29). You can't remove it from the cell that formatted with Custom format you just need to change format to the cell. If you have ° attached to the number then formula will work just fine. you can also use this one

    =REPLACE(B29,LEN(B29),1,"")-9.8*((D29/1000)-(C29/1000))
    Reply With QuoteRegister To Reply
  11. 12-05-2013, 02:27 PM #11
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: How to exclude special characters like °

    Thank you for your feedback!

    Don't forget to mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. To Call All Value Exclude value consist of special words
    By cyee in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-27-2013, 06:12 AM
  2. Macro to replace European characters with non "special" English characters?
    By johanna0507 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-19-2012, 09:13 AM
  3. Include/Exclude Specific Instring Characters From A Querry Search
    By bdb1974 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 01-05-2010, 03:43 PM
  4. Configuring SUM formula to include or exclude certain characters?
    By bobschwenkler in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-08-2008, 07:01 PM
  5. [SOLVED] Special characters
    By Matthew S in forum Excel General
    Replies: 10
    Last Post: 09-23-2005, 08:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1