Function or macro to extract data from a unstructured cell containing text

Hello
I'm trying to find a way to extract a User ID from cells in a field called Metadata. The data is not in a similar format in each cell and I've been racking my brain trying to figure out how to easily extract the User ID to an adjacent cell so I can run it easily daily without having to manually pull it out. The example below is just 2 examples but the spreadsheet has many rows of similar data in the Metadata column. Is there a way I could extract the data between "User ID" and the comma after it, such as "A0019015260" in the first row, and "B757182" in the second row as per the desired results below? I just can't think of a way to do it. Can anyone help me???

Column A Metadata
Row 1
Metadata (Column Title)
Row 2
[Product:ABC, DEF, Question:[Location:https://webaddress.html, Source:Widget, User ID:A0019015260, Product:SAP ASE, ERP enablement]]
Row 3
[Product:GHI, Question:[Source:Form, product:GHI, User ID:B757182, Location:http://webaddress.html]]

Column B
Row 1
User ID (Column Title)
Row 2
A0019015260
Row 3
B757182
tkeifferAsked:
Who is Participating?
 
Martin LissConnect With a Mentor Older than dirtCommented:
Add this user defined function to a module. Usage =userid(A1)

Function UserID(r As Range) As String
Dim strTemp As String
Dim intPos As Integer

strTemp = Split(r, "User ID:")(1)
intPos = InStr(1, strTemp, ",")
UserID = Left(strTemp, intPos - 1)
End Function

Open in new window

29080666.xlsm
0
 
tkeifferAuthor Commented:
That is amazing.  You made it look so easy.  :).  Thank you so much!!!
0
 
Martin LissOlder than dirtCommented:
Things are often easier when you've done it for 40+years.

You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2017
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
tkeifferAuthor Commented:
Hi Martin,
For some reason I'm getting an error on some of them like the value below.  It appears that if the data comes at the end of the string there is no comma.  Is there a way to stop at either a comma or a a bracket? "]" in the same module?

[Product:ABD BlahblahBlah Learning ABC Module, Question:[Location:http://www.test.com/abcv/index.html, product:ABD BlahblahBlah Learning ABC Module, Source:Form, User ID:S0012221212]]
0
 
Martin LissOlder than dirtCommented:
Replace the UserID function with this which strips off any non-alphanumeric characters at the end
Function UserID(r As Range) As String
Dim strTemp As String
Dim intPos As Integer

strTemp = Split(r, "User ID:")(1)
intPos = InStr(1, strTemp, ",")
If intPos > 0 Then
    UserID = Left(strTemp, intPos - 1)
Else
    For intPos = 1 To Len(strTemp)
        Select Case Asc(Mid(strTemp, intPos, 1))
            ' a to z, A to Z
            Case 97 To 122, 65 To 90, 48 To 57
                'OK
                Debug.Print UserID
                UserID = UserID & Mid(strTemp, intPos, 1)
        End Select
    Next
End If
End Function

Open in new window

Or if the length of the user id is always 11 then it can be done more simply with this.
Function UserID(r As Range) As String
Dim strTemp As String

strTemp = Split(r, "User ID:")(1)

UserID = Mid(strTemp, 1, 11)

End Function

Open in new window

0
 
tkeifferAuthor Commented:
Perfection again.  Thank you Marin.  I'm definitely going to check out the articles you wrote and are linked on your profile!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.