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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.