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

    extract specified text from column (IE 5.5)

    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

  2. #2
    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 specified text from column (IE 5.5)

    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

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

    Re: extract specified text from column (IE 5.5)

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

    regards
    indra

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

    Re: extract specified text from column (IE 5.5)

    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.

  5. #5
    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 specified text from column (IE 5.5)

    <P ID="edit" class=small>(Edited by sdckapr on 24-May-06 12:18. Added PS)</P>You can use a custom function.

    Add this to a module in the workbook:
    <pre>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</pre>


    Then you can enter in C2 (expand the range of abbreviations as desired)
    <pre>=GetAbbr(A2,'list abbrevation'!$A$3:$A$8)</pre>


    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:
    <pre> If InStr(UCase(sDesc), UCase(rCell.Value)) <> 0 Then</pre>


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

    Re: extract specified text from column (IE 5.5)

    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

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

    Re: extract specified text from column (IE 5.5)

    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

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

    Re: extract specified text from column (IE 5.5)

    See <post:=539,691>post 539,691</post:> 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.

Posting Permissions

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