+ Reply to Thread
Results 1 to 8 of 8

Excel not recognising cell values (urgent help)

  1. 01-20-2015, 06:07 PM #1
    GEOTGA
    GEOTGA is offline
    Registered User
    Join Date
    01-20-2015
    Location
    New Zealand
    MS-Off Ver
    2013
    Posts
    3

    Question Excel not recognising cell values (urgent help)

    Hi All,

    I'm using Excel 2013 and having trouble getting excel to recognise the value as a number.

    I have tried a number of "fixes" but cant figure out what is going on.

    - I cannot change the cell format to a 'number' - still recognises as text.

    - There doesn't appear to be any spaces or , . that excel cant recognise (otherwise I would use the find and replace function).

    I hopefully have attached the file I am having a problem with... It was created from some software and exported to excel.

    There are some working columns and some that do not. (i.e 'Peak Hz' values).

    I will have plenty of data which I don't want to manually enter.

    Please help

    Cheers
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 01-20-2015, 06:21 PM #2
    MrShorty
    MrShorty is offline
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,829

    Re: Excel not recognising cell values (urgent help)

    When I selected many of the cells, I could see the number in the cell, but could not see the number in the formula bar (I only had one line in the formula bar). By expanding the formula bar, I could see the number with something like a carriage return or line feed. By using the =CODE() function [=CODE(MID($A$5,column(A1),1)) then copy across] I could see the text code number for each character in A5, and there were indeed code 13's and code 10's to indicate carriage returns and line feeds. I'm not sure what the best way is to clean these characters from the cells, but it seems that these are the characters you need to find and get rid of.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.
    Reply With QuoteRegister To Reply
  3. 01-20-2015, 06:27 PM #3
    oeldere
    oeldere is offline
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Excel not recognising cell values (urgent help)

    You can run the code below for the problem in column E.

    After that you will be able to count with the values.

    Please Login or Register  to view this content.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.
    Reply With QuoteRegister To Reply
  4. 01-20-2015, 06:28 PM #4
    MarvinP
    MarvinP is offline
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,168

    Re: Excel not recognising cell values (urgent help)

    He Geotga and welcome to the forum,

    You have a lot of Carriage Retune Life Feed characters in front of stuff. Run this macro to remove all of them in your data.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by MarvinP; 01-20-2015 at 06:32 PM.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.
    Reply With QuoteRegister To Reply
  5. 01-20-2015, 06:36 PM #5
    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: Excel not recognising cell values (urgent help)

    You can also use formula to clean your data from Code 13 and Code 10

    =--SUBSTITUTE(SUBSTITUTE(A5,CHAR(10),""),CHAR(13),"")
    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
  6. 01-20-2015, 06:40 PM #6
    MrShorty
    MrShorty is offline
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,829

    Re: Excel not recognising cell values (urgent help)

    Looking through the list of available functions, there is also the =CLEAN() function that may be useful. https://support.office.com/en-us/art...5-4b8ba987ba41
    Reply With QuoteRegister To Reply
  7. 01-20-2015, 06:46 PM #7
    GEOTGA
    GEOTGA is offline
    Registered User
    Join Date
    01-20-2015
    Location
    New Zealand
    MS-Off Ver
    2013
    Posts
    3

    Re: Excel not recognising cell values (urgent help)

    Thanks for the quick response guys!

    Amazing stuff... much appreciated.

    I prefer the macro option as it is less work to remove from each cell. Marvin - how do apply this macro to Sheets 2 and 3 for example?
    (I downloaded your version and it works perfectly on sheet 1 but not sheet 2).

    Sorry for being such a dummy
    Reply With QuoteRegister To Reply
  8. 01-20-2015, 06:56 PM #8
    GEOTGA
    GEOTGA is offline
    Registered User
    Join Date
    01-20-2015
    Location
    New Zealand
    MS-Off Ver
    2013
    Posts
    3

    Re: Excel not recognising cell values (urgent help)

    Nevermind, I just had to select which sheet I was working on.

    Thanks again for every ones help on this!
    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. [SOLVED] Urgent Help with regards to Date cell values and format
    By rv02 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-06-2015, 11:13 AM
  2. Urgent : Inserting the word OR between the cell values in excel
    By shadowknight in forum Excel General
    Replies: 8
    Last Post: 11-29-2014, 05:07 PM
  3. Excel not recognising cell formats
    By Fungijus in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 10-31-2011, 05:30 PM
  4. Not recognising two values are the same
    By Big Mark in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-17-2008, 07:52 AM
  5. Function Not Recognising Values in Populated Ranges
    By Matt Roberts in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-11-2006, 11:35 AM

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