+ Reply to Thread
Results 1 to 8 of 8

Position of first non-alphanumeric character in a string without VBA Code

  1. 04-12-2021, 12:24 PM #1
    mmasher99
    mmasher99 is offline
    Registered User
    Join Date
    10-14-2015
    Location
    USA
    MS-Off Ver
    2010
    Posts
    4

    Position of first non-alphanumeric character in a string without VBA Code

    I need a non-VBA formula or array formula that provides me with the location of the first non-alphanumeric character in a string. So, Ascii code is NOT 65-90, 97-122, nor 48-57 inclusive.
    Reply With QuoteRegister To Reply
  2. 04-12-2021, 01:54 PM #2
    Bernie Deitrick
    Bernie Deitrick is offline
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,947

    Re: Position of first non-alphanumeric character in a string without VBA Code

    For a string in cell A2, Array-Enter - enter using Ctrl-Shift-Enter:

    =MIN(IF(ISERROR(SEARCH(MID(A2,ROW(INDIRECT("A1:A"&LEN(A2))),1),"0123456789qwertyuiopasdfghjklzxcvbnm")),ROW(INDIRECT("A1:A"&LEN(A2)))))

    Or if you like things in order

    =MIN(IF(ISERROR(SEARCH(MID(A2,ROW(INDIRECT("A1:A"&LEN(A2))),1),"0123456789abcdefghijklmnopqrstuvwxyz")),ROW(INDIRECT("A1:A"&LEN(A2)))))
    Bernie Deitrick
    Excel MVP 2000-2010
    Reply With QuoteRegister To Reply
  3. 04-12-2021, 02:49 PM #3
    mmasher99
    mmasher99 is offline
    Registered User
    Join Date
    10-14-2015
    Location
    USA
    MS-Off Ver
    2010
    Posts
    4

    Re: Position of first non-alphanumeric character in a string without VBA Code

    Hi Bernie, really appreciate quick response.
    Array formula seems to work in nearly all cases. However, it doesn't appear to recognize an asterisk as a non-alphanumeric value.
    "HARRIS*Johnson #0 1452 Albany #5122" ---> formula calculates 15.
    Reply With QuoteRegister To Reply
  4. 04-12-2021, 02:59 PM #4
    Bernie Deitrick
    Bernie Deitrick is offline
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,947

    Re: Position of first non-alphanumeric character in a string without VBA Code

    * and ? will give you problems - so use this array-entered:

    =MIN(IFERROR(SEARCH("~?",A2),LEN(A2)+1),IFERROR(SEARCH("~*",A2),LEN(A2)+1),IFERROR(1/(1/MIN(IF(ISERROR(SEARCH(MID(A2,ROW(INDIRECT("A1:A"&LEN(A2))),1),"0123456789abcdefghijklmnopqrstuvwxyz")),ROW(INDIRECT("A1:A"&LEN(A2)))))),LEN(A2)+1))
    Reply With QuoteRegister To Reply
  5. 04-12-2021, 03:09 PM #5
    mmasher99
    mmasher99 is offline
    Registered User
    Join Date
    10-14-2015
    Location
    USA
    MS-Off Ver
    2010
    Posts
    4

    Re: Position of first non-alphanumeric character in a string without VBA Code

    Attachment 728029
    Attachment 728030
    Reply With QuoteRegister To Reply
  6. 04-12-2021, 03:12 PM #6
    Bernie Deitrick
    Bernie Deitrick is offline
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,947

    Re: Position of first non-alphanumeric character in a string without VBA Code

    Sorry - your attachments come up as invalid.
    Attached Files Attached Files
    Last edited by Bernie Deitrick; 04-12-2021 at 03:16 PM.
    Reply With QuoteRegister To Reply
  7. 04-12-2021, 03:12 PM #7
    mmasher99
    mmasher99 is offline
    Registered User
    Join Date
    10-14-2015
    Location
    USA
    MS-Off Ver
    2010
    Posts
    4

    Re: Position of first non-alphanumeric character in a string without VBA Code

    Hi Bernie, Sorry for crossed notes. Revised formula works great!
    You are a formula wizard!
    Reply With QuoteRegister To Reply
  8. 04-12-2021, 04:58 PM #8
    Rick Rothstein
    Rick Rothstein is offline
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,410

    Re: Position of first non-alphanumeric character in a string without VBA Code

    If you know a maximum length that you text will never be longer than (I have assumed 100 but you can change the two 100s if 100 is too small), then this much shorter array-entered** formula will also work...
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    **Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself


    NOTE: My formula and Bernie's return different results when the text does not contain a non-alphanumeric character and when the text is blank (my formula returns 0 for both).
    Last edited by Rick Rothstein; 04-12-2021 at 05:51 PM.
    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. [SOLVED] copy alphanumeric character string of differing lengths to another column
    By karma2400 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-17-2014, 10:05 AM
  2. [SOLVED] find the character position in a string of the last occurrence of a nominated character
    By jmac1947 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-04-2014, 11:52 PM
  3. Finding the nth position of a character within a text string
    By StevenAlberta in forum Excel General
    Replies: 5
    Last Post: 10-11-2013, 04:04 AM
  4. Determine the position of an uppercase character in a string
    By blaasoppie in forum Excel General
    Replies: 10
    Last Post: 11-24-2010, 03:21 PM
  5. Remove alpha character in alphanumeric string
    By fun4four in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 07-28-2010, 04:22 PM
  6. Find Character Position in String
    By SportsDave in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-21-2006, 04:49 PM
  7. Function to return Character Position of Xth character within a string
    By Andibevan in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-09-2005, 11: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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1