+ Reply to Thread
Results 1 to 5 of 5

SEARCH with wildcards

  1. #1
    Registered User
    Join Date
    01-31-2008
    MS-Off Ver
    2010
    Posts
    38

    SEARCH with wildcards

    Hi all

    I'm trying to extract information out of a text string in a single cell. This one cell contains the User Name, Date Logged on, PC Service Tag, PC Model and last logon location all written in one line. e.g:

    Rachel Jones 24/08/2009 JMT9P1J-OptiPlex 330 Location: DERBY

    However, the script that builds this single line of text can sometimes get written the wrong way round and ends up like this:

    24/08/2009 Rachel Jones JMT9P1J-OptiPlex 330 Location: DERBY

    As you can see the Date and the name get switched.

    I'm looking for something that will allow me to check which 'format' the descriptions are in so that I can create the right formulas to extract the individual info. (Ultimately I would like to pull User Name, Date Logged on, PC Service Tag, PC Model and last logon location and stick them in their own cell.)

    I have used the SEARCH function and wildcards to pick out the name, e.g:

    =SEARCH("* ??/??/???? ???????-",C2,1)

    This works fine for the 'Name First' type but when it meets the 'Date First' type it displays #VALUE!. I can easily create a second 'mask' to pick up the 'Date First' format but I still end up with a load of #VALUE! around which are causing problems when trying to use the data later. (I guess that's because the SEARCH function can't find the string so it gives this error.)

    Is it possible to create a formula that checks to see if it's Format1 or Format2 and returns the value "Format1" or "Format2" (or "Error" if neither are correct)?

    Any help is much appreciated.

    Brelin

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: SEARCH with wildcards

    You can validate which based on whether or not the first 10 chars equate to a date or not...ie

    Please Login or Register  to view this content.
    Pending your set-up this could obviously get quite convoluted...

  3. #3
    Valued Forum Contributor
    Join Date
    10-15-2007
    Location
    Home
    MS-Off Ver
    Office 2010, W10
    Posts
    373

    Re: SEARCH with wildcards

    Hi

    From your pattern I take that the date has always the format 00/00/0000 and the PC Service Tag has always 7 characters and is followed by a "-".

    If that's the case try:

    =IF(ISNUMBER(SEARCH("* ??/??/???? ???????-",A2,1)),"Format 1",IF(ISNUMBER(SEARCH("??/??/???? * ???????-",A2,1)),"Format 2","Invalid format"))

  4. #4
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: SEARCH with wildcards

    It might not even be necessary to determine the format and then extract the data. This formula, for instance, should extract the date from the data in either format

    =MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1234567890)),10)+0

    format result cell in required date format, e.g. dd/mm/yyyy

  5. #5
    Registered User
    Join Date
    01-31-2008
    MS-Off Ver
    2010
    Posts
    38

    Re: SEARCH with wildcards

    Ahhh ISNUMBER ...I've used that before some time ago, forgot all about it!

    Thanks for all the responses I'll take another look at it now.

    Cheers

    Brelin

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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