Results 1 to 12 of 12
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Extract Data (Excel 2002)

    Edited to add file

    Hi
    I am Having a problem extracting text

    Example: Cell contains ARTHUR HOUSE CRAEGMOOR(A) (DN009) I would like to extract just DN009 without the brackets

    Many thanks

    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: Extract Data (Excel 2002)

    There is probably a solution using (array) formulas only, but I would use a small custom VBA function here:

    Public Function Extract(aValue As Variant) As String
    Dim lngStart As Long
    Dim lngLength As Long
    lngStart = InStrRev(aValue, "(")
    lngLength = Len(aValue)
    If lngStart > 0 Then
    Extract = Mid(aValue, lngStart + 1, lngLength - lngStart - 1)
    End If
    End Function

    Copy this into a standard module. Say that the text is in cell A1. Use the formula

    =Extract(A1)

    to get the text between the last pair of parentheses. This version doesn't do any consistency checking, post back if you need that.

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Extract Data (Excel 2002)

    Hi Hans

    I would appreciate consistency checking.

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Extract Data (Excel 2002)

    Hi Hans

    I just gave the function a try and got the error that function is not valid!

    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: Extract Data (Excel 2002)

    See attachment.

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

    Re: Extract Data (Excel 2002)

    The privacy warning is because I selected the option to remove personal information from the file before saving it.

    Make sure that you have copied the function into a standard module (the kind you create with Insert | Module) in the workbook you're using. If you copy it into a module in your Personal.xls, use it like this:

    =Personal.xls!Extract(A1)

    If that still doesn't work, try changing the name of the function, both in the code and in the formula, e.g to ExtractText:

    Public Function ExtractText(aValue As Variant) As String
    Dim lngStart As Long
    Dim lngEnd As Long
    If Not Right(aValue, 1) = ")" Then Exit Function
    lngStart = InStrRev(aValue, "(")
    If lngStart = 0 Then Exit Function
    lngEnd = Len(aValue)
    ExtractText = Mid(aValue, lngStart + 1, lngEnd - lngStart - 1)
    End Function

    =ExtractText(A1)

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Extract Data (Excel 2002)

    Hi Hans

    That's works just fine.

    Many thanks

    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: Extract Data (Excel 2002)

    So what did you have to change to make it work?

  9. #9
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Extract Data (Excel 2002)

    Hi Hans

    I just used you new code using =ExtractText(A1) instead of Extract(A1).

    Regards

    Braddy
    If you are a fool at forty, you will always be a fool

  10. #10
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Extract Data (Excel 2002)

    Large screenshot with lots of useless white space reduced in size by HansV

    Hi Hans

    I have a dilemma when I use your demo file it works just fine (except for the privacy warning on save).

    But when I try to apply your function ot another workbook I get the error as attached.

    Any Ideas please

    Braddy
    If you are a fool at forty, you will always be a fool

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Extract Data (Excel 2002)

    Hi Braddy,
    For data in E9 you could use:
    =SUBSTITUTE(RIGHT(E9,MATCH("(",MID(E9,LEN(E9)-ROW($1:$100),1),0)),")","")
    array-entered. (NB: This assumes that no cell is longer than 100 characters).
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Extract Data (Excel 2002)

    Hi Rory

    Thank you very much for you input.

    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

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