Excel to get certain text from data

Hello All,

Sorry to bother you guys. Kinda need some help on a formula.
I have an attached file where I have loads of data in column J. I need it to only locate a number starting with C00* and display it in column L. I tried using array formula and Find formula but it still does not get me the value I need.

I have put the Example output in column L as how I need it. Is this possible to be done?
If there is multiple C00* then it should also display that and if there is none found then show as "Not Found".

Hope someone is able to help. :-)

Kind Regards,
Mark.

Hi,

Is VBA allowed for the solution? It will make returning multiple matches much simpler I believe. Also, are all the codes the same length as they appear to be in your example data?

Originally Posted by xlnitwit

Hi,

Is VBA allowed for the solution? It will make returning multiple matches much simpler I believe. Also, are all the codes the same length as they appear to be in your example data?

Hi xlnitwit,

Would be great if there is such a macro which can do this. Would make my life easier but I'm not sure how to do so. :-(

Kind Regards,
Mark.

Originally Posted by xlnitwit

It looks like the same code can appear several times in one cell? In such a case, do you want the function to return it multiple times or should it only return a list of unique values?

Hi xlnitwit,

Yes it should only capture the unique values in each cell. :-)

Kind Regards,
Mark.

All right then - here is the revised function code:

Function MatchNumbers(sText As String, Optional sMissing As String = "Not found") As String
    Static myRegEx            As Object
    Dim all_matches
    Dim match
    If myRegEx Is Nothing Then Set myRegEx = CreateObject("vbscript.regexp")

    With myRegEx
        .Global = True
        .Pattern = "(\bC00\d+)"
        Set all_matches = .Execute(sText)
        Select Case all_matches.Count
            Case 0
                MatchNumbers = sMissing
            Case 1
                MatchNumbers = all_matches(0)
            Case Else
                With CreateObject("Scripting.Dictionary")
                    For Each match In all_matches
                        .Item(CStr(match)) = Empty
                    Next
                    MatchNumbers = Join(.keys, ",")
                End With
        End Select
    End With
End Function

Originally Posted by xlnitwit

All right then - here is the revised function code:

Function MatchNumbers(sText As String, Optional sMissing As String = "Not found") As String
    Static myRegEx            As Object
    Dim all_matches
    Dim match
    If myRegEx Is Nothing Then Set myRegEx = CreateObject("vbscript.regexp")

    With myRegEx
        .Global = True
        .Pattern = "(\bC00\d+)"
        Set all_matches = .Execute(sText)
        Select Case all_matches.Count
            Case 0
                MatchNumbers = sMissing
            Case 1
                MatchNumbers = all_matches(0)
            Case Else
                With CreateObject("Scripting.Dictionary")
                    For Each match In all_matches
                        .Item(CStr(match)) = Empty
                    Next
                    MatchNumbers = Join(.keys, ",")
                End With
        End Select
    End With
End Function

Hi xlnitwit,

Tried that and it returns a failed value #NAME?. Npt sure if im doing this correctly?

Kind Regards,
Mark.

Originally Posted by mark888

Hi xlnitwit,

Tried that and it returns a failed value #NAME?. Npt sure if im doing this correctly?

Kind Regards,
Mark.

Hi xlnitwit,

Yes I tried it again. I made a mistake. It works perfectly as a function. Many thanks sir for your help. :-)

Kind Regards,
MArk.

By the way, you didn't answer my earlier question but if all the codes are the same length, you could amend the pattern in the function code to use:

        .Pattern = "(\bC00\d{7})"

You are most welcome.