+ Reply to Thread
Results 1 to 11 of 11

How to exclude special characters like

  1. #1
    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

  2. #2
    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

  3. #3
    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

  4. #4
    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.

  5. #5
    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.:-)

  6. #6
    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!

  7. #7
    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?

  8. #8
    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.

  9. #9
    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 0c any idea why it returned that value?
    Last edited by barc0d3guy; 12-05-2013 at 12:20 PM.

  10. #10
    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))

  11. #11
    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 to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1