Results 1 to 15 of 15
  1. #1
    Star Lounger
    Join Date
    Jul 2005
    Location
    Jakarta, Indonesia
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    extract specific abbrev to other column (excell)

    Previously, I've got this function to extract specific abbreviation in a column to other column,

    Option Explicit
    Function GetAbbrS(sDesc As String, rAbbr As Range)
    Dim rCell As Range
    For Each rCell In rAbbr
    If InStr(sDesc, rCell.Value) <> 0 Then
    GetAbbrS = rCell.Value
    Set rCell = Nothing
    Exit Function
    End If
    Next
    GetAbbrS = CVErr(xlErrNA)
    Set rCell = Nothing
    End Function

    But if I have 2 different abbreviation example YOG and YOGA, that only extract YOG for both abbreviation
    Is there any suggest to modify?

    Best regards,

    Indra

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: extract specific abbrev to other column (excell)

    The function will return the first abbreviation it finds. What would you like instead? Where should multiple abbreviations be returned? Please be as specific as possible.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: extract specific abbrev to other column (excell)

    If you put the longer abbreviations in your list closer to the top than the shorter, it will do what I think you want. It will find the longer one first if it matches...

    Steve

  4. #4
    Star Lounger
    Join Date
    Jul 2005
    Location
    Jakarta, Indonesia
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: extract specific abbrev to other column (excell)

    I want the result for both abbrev,

    attached my working file

    regards,

    Indra

  5. #5
    Star Lounger
    Join Date
    Jul 2005
    Location
    Jakarta, Indonesia
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: extract specific abbrev to other column (excell)

    Hi Steve,

    But ussually I sort them out first, to avoid duplication, since I work with files from difference area.
    sorry too much to ask for your original macros

    -indra-

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: extract specific abbrev to other column (excell)

    How do you want both abbreviations? In one cell, or in two cells?
    If in one cell, how? Separated by a space, or by a comma, or ...?
    Again, please try to be specific.

  7. #7
    Star Lounger
    Join Date
    Jul 2005
    Location
    Jakarta, Indonesia
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: extract specific abbrev to other column (excell)

    sorry my english not making clear,

    both abbrev example will not appear in same transaction/row, but I want to extract exactly YOGA as YOGA not YOG,
    actually please refer to highlight column in file attached before

    thx

    Indra

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: extract specific abbrev to other column (excell)

    As Steve suggested, try sorting the Office range in descending order.

  9. #9
    Star Lounger
    Join Date
    Jul 2005
    Location
    Jakarta, Indonesia
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: extract specific abbrev to other column (excell)

    It cross my mind, but the description is
    BENY~REPORT PC8392

    I have abbrev BENY and EP, hopefully the result will be BENY instead of EP

    regards,

    Indra

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: extract specific abbrev to other column (excell)

    Can you explain exactly how the function should decide which abbreviation should be selected, if there is more than one?

  11. #11
    Star Lounger
    Join Date
    Jul 2005
    Location
    Jakarta, Indonesia
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: extract specific abbrev to other column (excell)

    normally it would not more than 1 abbrev will appears YOGYA will consider as 1 abbrev; YOGYA only, not YOGYA and YOG.

    if there 2 abbrev, I would like to extract the most left in description

    thanks

    Indra

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: extract specific abbrev to other column (excell)

    Would you like to check every abbreviation in the list and then find the longest one that matches? If there are more than 1 with an equal length found to use the one closest to the front of the of the search?

    Searching every abbreviation could make it a little sluggish. As it is now, it searches just until it finds a match which is more efficient.

    Steve

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: extract specific abbrev to other column (excell)

    If you have YOG and YOGYA they are both equally "left"....

    Steve

  14. #14
    Star Lounger
    Join Date
    Jul 2005
    Location
    Jakarta, Indonesia
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: extract specific abbrev to other column (excell)

    Hi Steve,

    It can't be both in one cell description..
    I mean if there is decription = YOGYA TIC SJET ,

    I want to extract YOGYA, not YOG
    Since SJET also on my abbrev list, it should not extracted.

    And may I recall from prev example, if description is
    BENY~REPORT PC8392
    I would extract BENY, not EP
    since I have also EP in my abbrev list

    regards from Yogyakarta

    Indra

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: extract specific abbrev to other column (excell)

    <P ID="edit" class=small>(Edited by sdckapr on 19-Jun-06 08:22. OOPS. Correction)</P>This requires that if they are "equally left" the longer will be chosen

    <pre>Function GetAbbrS(sDesc As String, rAbbr As Range)
    Dim rCell As Range
    Dim iFind As Integer
    Dim iLeft As Integer
    Dim iLen As Integer
    Dim sTemp As String
    iLen = 0
    iLeft = Len(sDesc)
    sTemp = ""

    For Each rCell In rAbbr
    iFind = InStr(sDesc, rCell.Value)
    If iFind <= iLeft And _
    iFind > 0 And _
    Len(rCell.Value) > iLen Then
    sTemp = rCell.Value
    iLen = Len(rCell.Value)
    iLeft = iFind
    End If
    Next
    If sTemp = "" Then
    GetAbbrS = CVErr(xlErrNA)
    Else
    GetAbbrS = sTemp
    End If
    Set rCell = Nothing
    End Function</pre>


Posting Permissions

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