+ Reply to Thread
Results 1 to 8 of 8

Nth character a number?

  1. #1
    Forum Contributor
    Join Date
    08-23-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    404

    Nth character a number?

    Hi all,

    I'm missing something obvious here but I can't see it. In A2 I have HE123 (formatted as 'General'). I am trying to test if the 3rd character is a number. For some reason the formula I have written returns FALSE.

    Please Login or Register  to view this content.

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Nth character a number?

    RIGHT function will return result as text. You need to convert it to number.
    Try:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Nth character a number?

    RIGHT function will return result as text. You need to convert it to number.
    Try:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  4. #4
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Nth character a number?

    Why not use MID?

    =ISNUMBER(--MID(A2,3,1))
    Cheers
    Andy
    www.andypope.info

  5. #5
    Forum Contributor
    Join Date
    08-23-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    404

    Re: Nth character a number?

    Hi both,

    Thanks for that both!

    @Andy - could you explain the use of '--' please?

  6. #6
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,609

    Re: Nth character a number?

    -- is one of ways to get text converted to numeric value. first minus negates it (if is "3" then becomes -3 and second negates again an we finish with 3.
    --yournumbertext is shorter in writing than VALUE(yournumbertext)

    Just a side comment.
    If we do -- etc on non-number we will get #Arg error. Which of course is not number, so ISNUMBER works fine.
    But if one would like not just check if it is number but also what number something shall be done about this error.
    for instance
    =IFERROR(--MID(A2,3,1),"")
    etc.
    Last edited by Kaper; 04-30-2014 at 05:10 AM.
    Best Regards,

    Kaper

  7. #7
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

  8. #8
    Forum Contributor
    Join Date
    08-23-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    404

    Re: Nth character a number?

    Hi Andy, Hi Kaper - thank you for the explanations. Sorry for the delay in responding.

+ 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. [SOLVED] Formula to remove first two character is second character is a number.
    By herbie226 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-20-2013, 02:59 PM
  2. Replies: 3
    Last Post: 11-20-2012, 10:03 PM
  3. Determine If Last Character is Number or Not A Number
    By chergh in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-23-2008, 10:45 AM
  4. remove cr character from a + number and change number to - 159cr
    By Cr character on numbers in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-10-2006, 06:40 PM
  5. [SOLVED] Why does last number of a 16 character number always change to 0?
    By FALCON in forum Excel General
    Replies: 2
    Last Post: 02-07-2006, 07:20 PM

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