+ Reply to Thread
Results 1 to 9 of 9

=left

  1. #1
    Registered User
    Join Date
    12-04-2016
    Location
    UK
    MS-Off Ver
    2016
    Posts
    79

    =left

    Hi all,

    Below I show the formula in each cell and then the 'output' in bold

    A1 : Input cell: 1 : 6
    A2 : =TRIM(LEFT(A1, SEARCH(":",A1,1)-1)) : 1
    A3 : =TRIM(RIGHT(A1,LEN(A1)-SEARCH(":",A1,SEARCH(":",A1)-1))) : 6
    A4 : =(A3*2) : 12
    A5 : =IF(A2>A4,"G","R") : G

    Currently, for some reason the final cell is showing G
    This is wrong, clearly A2 (1) is not greater than A4 (12)

    However, if I replace the formula in A2 by just typing the number "1", cell A5 will change to correctly displaying "R"

    What is it that is wrong with cell A2 that means that whilst displaying "1" correctly, when using that "1" in a further calculation an error occurs.

    Thanks

  2. #2
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: =left

    check if your "numbers" are real numbers: ISNUMBER(A2) etc

    and in
    A2 : =--TRIM(LEFT(A1, SEARCH(":",A1,1)-1))
    A3 : =--TRIM(RIGHT(A1,LEN(A1)-SEARCH(":",A1,SEARCH(":",A1)-1)))
    Last edited by sandy666; 11-08-2017 at 10:16 AM.

  3. #3
    Registered User
    Join Date
    12-04-2016
    Location
    UK
    MS-Off Ver
    2016
    Posts
    79

    Re: =left

    ISNUMBER returns FALSE for cell A2 and TRUE for cell A3.
    Not sure why that is.
    The number format is set to 'number' for both

  4. #4
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: =left

    A2 is indeed NOT a number.
    The LEFT function returns a TEXT string, even if it looks like a number.
    A4 is a REAL number.

    This is the strange part, TEXT strings are considered Greater than Numbers by the > and < operators.


    Change A2 to
    TRIM(LEFT(A1, SEARCH(":",A1,1)-1))+0

    And A3 as well
    =TRIM(RIGHT(A1,LEN(A1)-SEARCH(":",A1,SEARCH(":",A1)-1)))+0

  5. #5
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: =left

    Add 0 to the formulas in A2 and A3.

    A2 and A3 are being seen as text. A4 multiplies A3 by a number so A4 is being seen as a number.

    Text is always seen as greater than a number.

  6. #6
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: =left

    result of formula is a text. cell format shows only visual look. like I said above use these two formulas instead of yours

  7. #7
    Registered User
    Join Date
    12-04-2016
    Location
    UK
    MS-Off Ver
    2016
    Posts
    79

    Re: =left

    Ahha, how strange that Excel would favour text over numbers.
    Thanks so much for sorting it out so swiftly all.

  8. #8
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: =left

    Glad we could help. Thanks for the rep!

  9. #9
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: =left

    Functions used by you in formula(s) are TEXT functions so result will be a TEXT. You need change text to number with these tricks:
    = --TRIM( ... etc
    or
    = 1*TRIM( ... etc
    or
    = 0+TRIM( ...

    thanks for rep

+ 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. Replies: 6
    Last Post: 06-26-2017, 09:04 PM
  2. [SOLVED] If 5 days left then MsgBox "5 Days Left"
    By HerryMarkowitz in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-12-2014, 11:59 AM
  3. Collapse columns from left to right (not right to left)
    By SiriS in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-26-2013, 04:00 PM
  4. [SOLVED] LEFT(A1,3) or LEFT(RC[-1],3) which one is more speedy ?
    By nur2544 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-27-2013, 02:23 AM
  5. Automated =Left, =Left(Right), =Right & Concatenate
    By mikeconomy in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-29-2011, 08:07 AM
  6. Replies: 1
    Last Post: 07-04-2005, 09:05 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1