Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Mar 2008
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Search cells for specific string of text (Excel 2003)

    Is it possible to search a cell that contains a string of text that is about 30 characters in length for a piece of text contained within that string. For example, I have a report provided by a business partner that contains a column with a description from a billing invoice. The description column usually contains about 30 characters of text and somewhere in this string is a 10 digit invoice ID that I need to extract out. The good news is the invoice ID always begins with 6305 but the problem I'm having is the invoice ID may start anywhere in the larger string. I'm trying to find a way to identify where in this string of text the invoice ID begins and what I need to do to extract it out. I do not need to remove the invoice ID from the description column, I would just need to identify it and paste it into a new column for later use. I've included and example spreadsheet showing what I'm trying to do. Any help with this would be greatly appreciated. Thanks!
    Attached Files Attached Files

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

    Re: Search cells for specific string of text (Excel 2003)

    Enter the following formula in E2 and fill down:

    =MID(A2,FIND("6305",A2),10)

  3. #3
    Star Lounger
    Join Date
    Mar 2008
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search cells for specific string of text (Excel 2003)

    Hans,

    Thank you for the reply. I apologize, but I should have elaborated some more on this. I'm trying to accomplish this is visual basic. The reason to do this in Visual basic is some times the description column does not actually contain and invoice ID. Some business partners actually put it in the correct column and their is no need to extract it out. By dragging down the formula, I would overwrite where some business partners have correctly put the invoice ID with nothing because the formula could not find 6305 in the description field and enters nothing as a result of the formula. By doing this in visual basic, I can perform some checks to see if the invoice ID is already in the correct column and if not check the description field to see if it is in there. I'm trying to find a way where I don't have to paste or enter any formulas into the sheet as I will not actually be performing the work myself. I'm hoping to hand off the macro to a team that will use it to make sure they can extract these invoice ID's where needed. Any help with this approach would be appreciated. Thanks!!

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

    Re: Search cells for specific string of text (Excel 2003)

    Here is a sample of the kind of code you can use. This fragment doesn't do anything, I'll leave that to you:

    Dim strValue As String
    Dim strInvoiceID As String
    Dim intPos As Integer
    strValue = Range("A2").Value
    intPos = InStr(strValue, "6305")
    If intPos = 0 Then
    ' Invoice ID not found in cell value
    Else
    strInvoiceID = Mid(strValue, intPos, 10)
    End If

  5. #5
    Star Lounger
    Join Date
    Mar 2008
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search cells for specific string of text (Excel 2003)

    Thank you Hans. I think this should be what I'm looking for. I'm sure I can make this work. Thanks again for your help!!!

  6. #6
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Hans. Exactly what I was looking for. A little tweaking and is just what the doctor ordered.

Posting Permissions

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