+ Reply to Thread
Results 1 to 8 of 8

= for text vs. numbers

  1. #1
    Registered User
    Join Date
    01-30-2006
    Posts
    14

    = for text vs. numbers

    @if(h3="soft";1;0) This is a valid Lotus123 formula. How does Excel interpret = where equals refers to text rather than to a number? This formula reads "if what is in h3 is the (equals) word "soft" then this formula equals 1 (true), otherwise it equals 0 (false). Thanks.

  2. #2
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: = for text vs. numbers

    I'm afraid that I do not remember Lotus 1-2-3.

    In a comparison of the form A1=B1, where ISTEXT(A1) is TRUE and ISTEXT(B1) is TRUE, A1 and B1 are equal IF LEN(A1)=LEN(B1), and they are the same character-for-character, except that "case" (i.e. uppercase and lowercase) might be differ.

    So IF(H3="soft";1;0) returns 1 if the value in H3 is "s" or "S", "o" or "O", "f" or "F", "t" or "T".

    If you want a case-sensitive comparison, you can write IF(EXACT(h3,"soft");1;0).

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: = for text vs. numbers

    wow that tales me back, I cut my teeth on 1-2-3.

    There is no real difference in how excel handles text vs numbers in formulas (except you obviously cant add text).
    The excel equivalent would be
    =if(h3="soft";0;1)
    and the logic is exactly the same as you described.

    what exactly are you trying to do?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: = for text vs. numbers

    Administrative note
    Please update your profile as necessary to properly reflect the exact version(s) of Excel your question relates to. Members tailor answers based on your Excel version. Your profile does not indicate your version.
    Thanks

  5. #5
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: = for text vs. numbers

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

    Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

    Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

    (Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

  6. #6
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: = for text vs. numbers

    Quote Originally Posted by FDibbins View Post
    There is no real difference in how excel handles text vs numbers in formulas (except you obviously cant add text).
    Just to confirm: are you saying the Lotus 1-2-3 did case-insensitive comparisons of text, just as Excel does?

    As for "adding" text, Excel permits us to reference text in arithmetic calculations, if Excel can recognize the text as a numeric form. For example, ="123"+"456" is the numeric result 579. ="123"*"1%" is the numeric result 1.23. ="1/2/2020"+"12" is the numeric date 1/14/2020, if the cell is formatted as Short Date, and if Excel recognizes "1/2/2020" as a date in the form MDY in your region.

    Are you saying that Lotus 1-2-3 did not permit text in arithmetic calculations, as Excel does?

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: = for text vs. numbers

    Pepe, I have restored joeu's post and reversed the warning, I believe this was simply a timing issue (9 mins btw postings), plus the comment was directed at me, not really at the OP's question

  8. #8
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: = for text vs. numbers

    Ford, I would have appreciated that you consulted me first, BTA, you're the boss..
    I sent you my resignation per PM.
    Cheers
    Last edited by Pepe Le Mokko; 04-30-2020 at 03:15 AM.

+ 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. Extract Text from Numbers Where Text and Numbers are Different # of Characters
    By EvolutionJulie in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-06-2019, 10:50 PM
  2. VBA to convert a range from numbers to numbers stored as text (Text to Columns)
    By tantcu in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-06-2017, 05:50 PM
  3. Replies: 11
    Last Post: 04-10-2017, 06:23 AM
  4. Replies: 19
    Last Post: 07-20-2014, 04:55 PM
  5. [SOLVED] Marco to convert numbers stored as text to numbers and dates stored as text to numbers
    By a2424 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-22-2014, 10:19 AM
  6. [SOLVED] [SOLVED] Macro/Wildcard to remove only numbers excluding text with numbers
    By indianhp in forum Word Programming / VBA / Macros
    Replies: 6
    Last Post: 06-21-2013, 08:16 AM
  7. [SOLVED] Convert all numbers stored as text or custom formatted to numbers &no decimals - 40 sheets
    By synses in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-19-2013, 01:46 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