+ Reply to Thread
Results 1 to 15 of 15

Excel Text split , text or number value

  1. 12-05-2023, 04:18 PM #1
    Dicken
    Dicken is offline
    Forum Contributor
    Join Date
    08-03-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    615

    Excel Text split , text or number value

    Hi,
    this is really just a theory question, if you have text in a cell , 1,2,3,a,b,c,d 9 ,

    and then use text spilt, you get a spill of text values ; so

    Please Login or Register  to view this content.
    but if you use a math operation, eg -- , the numbers act a numbers and the 'text as text and return a false, yet the istext
    function has just returned text for all the values,

    so are they text or number in spill as above, I've attached sheet, this was about removing numbers but this question arose; 

    Please Login or Register  to view this content.
    Richard.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 12-05-2023, 05:00 PM #2
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,463

    Re: Excel Text split , text or number value

    Build up bit by bit, maybe

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  3. 12-05-2023, 05:10 PM #3
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,463

    Re: Excel Text split , text or number value

    so are they text or number in spill as above
    The output from any Text function will return text output, even if it looks like a number. That includes LEFT, RIGHT, MID, TEXTSPLIT, TEXTBEFORE, TEXTAFTER, etc
    Reply With QuoteRegister To Reply
  4. 12-06-2023, 08:40 AM #4
    Dicken
    Dicken is offline
    Forum Contributor
    Join Date
    08-03-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    615

    Re: Excel Text split , text or number value

    Thanks, it was more about how text values behave than anything.
    Reply With QuoteRegister To Reply
  5. 12-06-2023, 08:55 AM #5
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,063

    Re: Excel Text split , text or number value

    Not sure what you're asking... but... as above

    TEXTSPLIT returns text (numbers are left-aligned and just look lin=ke numbers. The -- converts number-like text to numbers. REAL alphabetical text doesn't like -- and returns a #VALUE error for which ISNUMBER returns FALSE.
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
    Reply With QuoteRegister To Reply
  6. 12-06-2023, 09:14 AM #6
    Dicken
    Dicken is offline
    Forum Contributor
    Join Date
    08-03-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    615

    Re: Excel Text split , text or number value

    Thanks, it's not sothing I'd really thought about, I had numbers as numbers and text as text, it was only when the

    -- or * was used on the spill from split i noticed the 'numbers' behaved like numbers


    so ISNUMBER ( 8 'as text ) = FALSE , but , 8 'as text' * 2 = 16 as number ? just seems weird.

    Richard.
    Reply With QuoteRegister To Reply
  7. 12-06-2023, 09:23 AM #7
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,063

    Re: Excel Text split , text or number value

    >>Some<< Excel functions are able to translate text numbers into real numbers, but by NO MEANS all of them are.

    Life's jut plain weird sometimes. So is excel.
    Reply With QuoteRegister To Reply
  8. 12-06-2023, 10:35 AM #8
    Dicken
    Dicken is offline
    Forum Contributor
    Join Date
    08-03-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    615

    Re: Excel Text split , text or number value

    Regarding that I don't know if you've seen this video, were if you can't use an array you use N( IF( 1, then formula ;


    Please Login or Register  to view this content.

    Richard
    Reply With QuoteRegister To Reply
  9. 12-06-2023, 12:25 PM #9
    rorya
    rorya is offline
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent Monthly Channel / Insiders Beta
    Posts
    8,913

    Re: Excel Text split , text or number value

    As I mentioned in your post on Answers, operators (like * or +) will coerce numeric text to real numbers, but most functions will not.
    Rory
    Reply With QuoteRegister To Reply
  10. 12-06-2023, 12:33 PM #10
    Dicken
    Dicken is offline
    Forum Contributor
    Join Date
    08-03-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    615

    Re: Excel Text split , text or number value

    Thanks,

    RD
    Reply With QuoteRegister To Reply
  11. 12-06-2023, 06:12 PM #11
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,463

    Re: Excel Text split , text or number value

    @Dicken: Just wondering why you gave me negative rep when your comment seemed positive?

    thread: Excel Text split , text...
    informative
    Reply With QuoteRegister To Reply
  12. 12-06-2023, 06:14 PM #12
    Dicken
    Dicken is offline
    Forum Contributor
    Join Date
    08-03-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    615

    Re: Excel Text split , text or number value

    Sorry, I did not realise I had it was meant to be positive, can I change this
    Reply With QuoteRegister To Reply
  13. 12-06-2023, 06:24 PM #13
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,463

    Re: Excel Text split , text or number value

    No, you can't change it. After you have given rep to someone else, you should be able to give +rep to me which will restore the status quo.

    Easy mistake. We've all done it at some point. Not to worry. As I said, just wondered.
    Reply With QuoteRegister To Reply
  14. 12-07-2023, 10:42 AM #14
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,463

    Re: Excel Text split , text or number value

    Thanks for the +rep
    Reply With QuoteRegister To Reply
  15. 12-07-2023, 11:11 AM #15
    Dicken
    Dicken is offline
    Forum Contributor
    Join Date
    08-03-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    615

    Re: Excel Text split , text or number value

    Your/re welcome.
    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. Split number from text
    By sanju2323 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-02-2018, 11:11 AM
  2. Formula: Split text + number
    By ScaniaR730 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 02-17-2017, 10:42 AM
  3. Help! Import text into Excel and split it into colunms based on the structure of the text
    By orange109 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-04-2017, 03:35 PM
  4. Split text entered in UserForm text box into two cells; split at space within sentence.
    By tpsmith in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-11-2015, 03:00 PM
  5. Split text and number but keep the last text at the end.
    By tantcu in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-16-2013, 06:28 PM
  6. split number and text
    By emina002 in forum Excel General
    Replies: 5
    Last Post: 07-02-2011, 07:22 PM
  7. Split text and number
    By Jambruins in forum Excel General
    Replies: 5
    Last Post: 10-05-2005, 05:05 PM

Tags for this Thread

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