Smog Movies

Id’s are in a format as such ##-#### (two numbers, a dash, and a few more numbers). Normally I could use a search function to find the dash within the text and extract the id to compare except this time the text block that I am searching through also contains many other dashes so I get false positives. An example would be to extract 07-8990 from:

the red apple fell off of the tree on 1-10-2009. The id number of the tree is 07-8990. It needs to be replanted on 10-10-2010.

or something like that. I am open to using vba or normal excel functions to pull the ID out.

Thanks

Here is a custom function that will get the ID from your text string. It will find the first "word" in your text that only has one dash. A "word" is any contiguous text or numbers separated by spaces.

To install the custom function…
Alt+F11 to open the VBA editor
From the VBA menu, select Insert\ Module
Paste the code below in the edit window.

Back in Excel…
If your text is in cell A1, use this formula to get the ID
=GETID(A1)

—

Function GETID(rng As Range) As String
Application.Volatile
Dim sWords() As String, i As Integer
sWords = Split(rng, " ")
For i = LBound(sWords) To UBound(sWords)
If Len(sWords(i)) - Len(Replace(sWords(i), "-", "")) = 1 Then
If Not IsNumeric(Right(sWords(i), 1)) Then
sWords(i) = Left(sWords(i), Len(sWords(i)) - 1)
End If
GETID = sWords(i)
Exit For
End If
Next i
Erase sWords
End Function

2 Responses

1. Tandelion Says:
   October 29th, 2009 at 8:37 pm

   That is somewhat tricky with the false positives. But if you know that the false positives will be in that date format, you can exclude those in your VBA function. I’m not sure if there’s a way to do this strictly with Excel functions. If there is, it would be nasty looking and hard to maintain imho.
   References :

2. Cozmosis Says:
   October 29th, 2009 at 8:52 pm

   Here is a custom function that will get the ID from your text string. It will find the first "word" in your text that only has one dash. A "word" is any contiguous text or numbers separated by spaces.

   To install the custom function…
   Alt+F11 to open the VBA editor
   From the VBA menu, select Insert\ Module
   Paste the code below in the edit window.

   Back in Excel…
   If your text is in cell A1, use this formula to get the ID
   =GETID(A1)

   —

   Function GETID(rng As Range) As String
   Application.Volatile
   Dim sWords() As String, i As Integer
   sWords = Split(rng, " ")
   For i = LBound(sWords) To UBound(sWords)
   If Len(sWords(i)) - Len(Replace(sWords(i), "-", "")) = 1 Then
   If Not IsNumeric(Right(sWords(i), 1)) Then
   sWords(i) = Left(sWords(i), Len(sWords(i)) - 1)
   End If
   GETID = sWords(i)
   Exit For
   End If
   Next i
   Erase sWords
   End Function
   References :

Leave a Comment