+ Reply to Thread
Results 1 to 4 of 4

changing a cell to number format using a formula

  1. #1
    Registered User
    Join Date
    02-08-2007
    Posts
    11

    changing a cell to number format using a formula

    Hello,

    I apologize if this is a very basic question but I am writing a template to analyze some data and I am having trouble with the formatting of my cells. I am extracting numerical values from other sheets in the workbook but I believe that Excel is not treating them as numbers. For instance, when I evaluate a formula is giving a False response to "1"<9. I believe that I can remedy this problem if I can adjust the format of the cells to 'numbers'. Is there a way to do this using formulae, or am I forced to resort to a macro?

    Thank you very much for your time.

    Paul

  2. #2
    Registered User
    Join Date
    08-17-2004
    Posts
    14

    Value Formula

    Try using the =Value("Reference")

  3. #3
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    How are you extracting the numbers? If you use quotes around numbers like "1" then they will be treated as text, also if you use functions like MID, LEFT or RIGHT then the results of these are also text, although you can adjust formulas to make them numbers, e.g.

    =LEFT(A1) will always give text but, as JRock suggests, assuming the the leftmost character of A1 is numeric you can convert to a number with

    =VALUE(LEFT(A1))

    or by performing some sort of mathematical operation on it (without changing the value) like this

    =LEFT(A1)+0

  4. #4
    Registered User
    Join Date
    02-08-2007
    Posts
    11

    Smile Thank you!

    I just wanted to say thanks to JRock and daddylonglegs for providing me with the solution to my problem.

    Paul

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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