+ Reply to Thread
Results 1 to 12 of 12

Highlight Irregular Data such as B채rbel?

  1. #1
    Registered User
    Join Date
    08-15-2018
    Location
    USA
    MS-Off Ver
    2016
    Posts
    6

    Highlight Irregular Data such as B채rbel?

    B채rbel

    For example.

    These are all names, first and last.

    I have 40K rows and need to find any data in any cells that is NOT:

    A through Z (upper or lower case), a space, a dash or an apostrophe (all allowed in names).

    Thanks for any help, this is driving me bonkers.

  2. #2
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Highlight Irregular Data such as B채rbel?

    Something like (not case sensitive).

    =SUMPRODUCT(SEARCH(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)," '-abcdefghijklmnopqrstuvwxyz"))

    Not sure how long that will take to process with 40k rows.

  3. #3
    Registered User
    Join Date
    08-15-2018
    Location
    USA
    MS-Off Ver
    2016
    Posts
    6

    Re: Highlight Irregular Data such as B채rbel?

    Found this via Google as well - it came up as an error in the formula - I did set it to the correct cell...
    Last edited by alansidman; 08-15-2018 at 10:25 PM.

  4. #4
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Highlight Irregular Data such as B채rbel?

    Sorry, I should have said, the idea is to enter it into the adjacent column.

    Valid names should return a number, anything with disallowed characters will give an error, then you could filter to show errors.

    It wasn't clear exactly how you wanted to highlight the incorrect entries, my thought was that this would be more useful than say red / green highlights which would still require you to scroll through the entire list.

  5. #5
    Registered User
    Join Date
    08-15-2018
    Location
    USA
    MS-Off Ver
    2016
    Posts
    6

    Re: Highlight Irregular Data such as B채rbel?

    I don't know how to customize the formula to my needs I guess. I entered in adjacent cell and changed the A2 in the example to point to the cell I am testing and on enter the formula reports an error.

  6. #6
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Highlight Irregular Data such as B채rbel?

    Did you change both A2's?

    Formula should return an error for irregular data or a number for proper names.

  7. #7
    Registered User
    Join Date
    08-15-2018
    Location
    USA
    MS-Off Ver
    2016
    Posts
    6

    Re: Highlight Irregular Data such as B채rbel?

    See attached.

    Hey, THANK YOU for helping me with this! :-)
    Attached Images Attached Images

  8. #8
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Highlight Irregular Data such as B채rbel?

    See if this helps, I can use the formula exactly as in your image with no error so guess you're using european regional settings?

    The attached file has a slightly different version of the formula in place, so it should automatically 'translate' to match your settings when you open it.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    08-15-2018
    Location
    USA
    MS-Off Ver
    2016
    Posts
    6

    Re: Highlight Irregular Data such as B채rbel?

    This is epic. EPIC. It's getting all names with special characters.

    How am I going to have it pull entries like A - a single character or DC, two single characters, or a BLANK - no data entered in the field.

    THANK YOU THANK YOU THANK YOU :-)

  10. #10
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Highlight Irregular Data such as B채rbel?

    Maybe something like

    =IF(LEN(B2)<3,"Error",IF(ISERROR(SUMPRODUCT(SEARCH(MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)," '-abcdefghijklmnopqrstuvwxyz"))),"Error",""))

    Use <3 so set the minimum number of characters in the name field.

    If you want to be able to quickly identify those separately from the invalid characters then you could change one of the "Error" messages.

    Were you able to identify the differences between the formula that didn't work and the one in the sample file that did?

  11. #11
    Registered User
    Join Date
    08-15-2018
    Location
    USA
    MS-Off Ver
    2016
    Posts
    6

    Re: Highlight Irregular Data such as B채rbel?

    Brilliant solution - it catches "John A." however. Thoughts?

    This is beyond the COD - I really appreciate it!

    EDIT - yes the original error was, of course, mine - I'm set to use pipes instead of commas as delimiters because of another system in which we work.

  12. #12
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Highlight Irregular Data such as B채rbel?

    Are you saying that "John A." is coming up as Error, but is in fact allowable?

    If that is the case then you would need to add the period to the string of permitted characters.

    The string " '-abcdefghijklmnopqrstuvwxyz" contains all of the permitted characters, you can change that as needed.

+ 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: 16
    Last Post: 12-04-2017, 04:53 PM
  2. [SOLVED] Find peaks in irregular data
    By Luuck234 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-29-2016, 03:35 AM
  3. Transposing irregular repetetive data
    By Braedos in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-13-2016, 01:41 AM
  4. Transposing irregular repetitive data
    By dctipster in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-08-2016, 12:01 PM
  5. [SOLVED] Parsing Irregular but well organized data
    By Taisir in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-16-2015, 12:35 AM
  6. Irregular Data Groups In A Column
    By birturkm in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-17-2011, 04:36 AM
  7. Sorting irregular data Preferably with VBA
    By daymo in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 11-06-2011, 04:55 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