Extract Text from a cell

To extract a string in a random spot in an Excel cell, I think you might need an add-in like Kutools

https://www.extendoffice.com/documents/excel/3639-excel-extract-part-of-string.html

With the text in A1 you can use

=IFERROR(MID(A1,FIND("CHARS",A1),FIND(",",A1,FIND("CHARS",A1))-FIND("CHARS",A1)),"")

Select allOpen in new window

It extract the value CHARS10133X166

Hi John, thanks for your quick support. the above plugin required a license...

Hi Ejgil, Thanks for your technically although when I run the formula it only leaves the cell blank...

Try the trial version and if it does what you want , then you can license it

You can use this user defined function.

Function FindString(r As Range) As String
Dim strParts() As String
Dim IntEntry As Integer

strParts = Split(r, ",")
For IntEntry = 0 To UBound(strParts)
    If strParts(IntEntry) Like "*CHARS*" Then
        FindString = strParts(IntEntry)
        Exit For
    End If
Next
    
End Function

Select allOpen in new window

Usage:
=FindString("A1")

A one-liner of VBA will do:

Public Function GetCode(FullCode) As String
    FindCode = Split(Split(FullCode, "CHARS")(1), ",")(0)
End Function

Select allOpen in new window

Thanks everyone involved ! I'd really appreciated