Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Hyphens and comparisons

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    81
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Here is a good one..

    I have an array filled with individual strings, the strings are something like this... A1D-WS-2346-78 and I would like to make the string like this A1DWS234678. Please be aware that the hyphens are in variable places and there are a variable number of hyphens.

    My ultimate aim is to compare two strings, call one the master and one the subject. The master is the one above, it contains a lot of information about a part, however the subject also contains the same information but only part of it, I need to compare the subject to every one of the master strings and find out if it appears anywhere within the master string...

    both ave hyphens but if i can remove the hyphens from both ten I can do a strait text comparison...

    One step at a time, I am trying to remove the hyphens and then I wil try and tackle the comparison, but any help on that would be very... and i mean very greatfuly recieved!!

    Cheers

    Ed

    PS, talk about summing diagonally.... this one hads gotta take the biscuit!! arrgghh

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,026
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    On 2002-04-17 03:05, Jackeb wrote:
    Here is a good one..

    I have an array filled with individual strings, the strings are something like this... A1D-WS-2346-78 and I would like to make the string like this A1DWS234678. Please be aware that the hyphens are in variable places and there are a variable number of hyphens.

    My ultimate aim is to compare two strings, call one the master and one the subject. The master is the one above, it contains a lot of information about a part, however the subject also contains the same information but only part of it, I need to compare the subject to every one of the master strings and find out if it appears anywhere within the master string...

    both ave hyphens but if i can remove the hyphens from both ten I can do a strait text comparison...

    One step at a time, I am trying to remove the hyphens and then I wil try and tackle the comparison, but any help on that would be very... and i mean very greatfuly recieved!!

    Cheers

    Ed

    PS, talk about summing diagonally.... this one hads gotta take the biscuit!! arrgghh
    [1]

    =ISNUMBER(SEARCH(B1,A1))

    returns TRUE if the substring in B1 is a substring of the string in A1.

    [2]

    =SUBSTITUTE(A1,"-","")

    removes hyphens if they exist in the string in A1.

    [3]

    =ISNUMBER(SEARCH(SUBSTITUTE(B1,"-",""),SUBSTITUTE(A1,"-","")))

    will return TRUE if the de-hyphenated substring in B1 is a substring of the de-hyphenated string in A1.

    Not sure whether you need de-hyphenating at all.


  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    81
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    sounds good... all i have to do now is put that into VBA and i am sorted... cheere,

    and i do need to dehyphenate... unfortunatly

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •