Search cells for specific string of text (Excel 2003)

Topic

New Reply

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!

Reply | Quote

Replies

Enter the following formula in E2 and fill down:

=MID(A2,FIND(“6305”,A2),10)

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Thanks Hans. Exactly what I was looking for. A little tweaking and is just what the doctor ordered.

Reply | Quote