extract specified text from column (IE 5.5)

Topic

New Reply

I have one column data that I want to extract specified abbrevation – which is listed – in to separate column so I can easily group the other column. Is there way to do automatically?

regards,

Indra

Reply | Quote

Replies

You can create a lookupof possibilities with their abbreviation.

many seemed to be able to be gotten with:
=MID(A2,2,4)

copied down a column, but there are some that do not work

=IF(ISNUMBER(MATCH(MID(A2,2,4),’list abbrevation’!$A$3:$A$8,0)),MID(A2,2,4),”???”)

copied down will guess at the abbreviation and if it is in the list, it will display it otherwise it will be “???” and you can manually just check on those

Steve

Reply | Quote

I’ve tried that before, but I have lot of data and still look for faster way.
Thanks anyway steve,

regards
indra

Reply | Quote

If the data don’t change very often, you could use a macro to fill a column with the abbreviations.
But you’d have to remember to run the macro each time the data change.

Reply | Quote

(Edited by sdckapr on 24-May-06 12:18. Added PS)

You can use a custom function.

Add this to a module in the workbook:

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

Then you can enter in C2 (expand the range of abbreviations as desired)

=GetAbbr(A2,'list abbrevation'!$A$3:$A$8)

And copy down column C. The function goes thru each of the values in the “abbreviation range” and sees if it is in the string. The first one found is considered the abbreviation and the function stops looking any more.. If none are found a #NA is given (change to something else if desired).

Steve

PS if you want the search to be non-case sensitive use the line:

      If InStr(UCase(sDesc), UCase(rCell.Value))  0 Then

Reply | Quote

this is exactly what I’ve been looking for!! i’m really grateful
thank you very much steve, thank you for the forum

regards,

indra

Reply | Quote

Hi guys, it’s me again.
where i can learn macro/vba like you did to my problem above, or any modul/website/book you could give me for reference?
I have enough in excel function, but in some case it not support anymore
I’ve tried (not hard enough I think,) to read some book and browse some material but my ability stuck in the level such as copy paste or only insert vba modul : )
I am an accountant for not profit organisation, and my objective is to learn any vba stuff relate my job.

Thanks again

indra

Reply | Quote

See post 539,691 for some book recommendations.

How to use Visual Basic for Applications in Excel contains links to Microsoft articles about Excel VBA.

If you search Google for excel vba tutorial you’ll find many online tutorials.

Reply | Quote