extract specific abbrev to other column (excell)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

I want the result for both abbrev,

attached my working file

regards,

Indra

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

If you have YOG and YOGYA they are both equally “left”….

Steve

Reply | Quote

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

Reply | Quote

(Edited by sdckapr on 19-Jun-06 08:22. OOPS. Correction)

This requires that if they are “equally left” the longer will be chosen

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  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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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-

Reply | Quote