Looking up multiple values from a single cell

Question

I have a data set where a lot of different categories and data were crammed into one cell.

For example, I have one cell that has names of individuals and a percentage:

Jess 15%, Frank 20%, Allan 50%, Steve 15%

I would like to find a function that can lookup and harvest the name of a single person and the associated percentage for that person from that single cell.

Answer 1

If your cell is A1 and your selected name in A3 please try:

=MID(A1,FIND(A3,A1)+LEN(A3),4)

Answer 2

If Text-To-Columns isn't an option because this is a field that is repeated across many records and the number of names in the field varies, then you could use a UDF to perform the search:

Function findPercent(searchname As String, namelist As String) As String
    Dim nameArray() As String

    nameArray = Split(namelist, ",")

    For arrCounter = 0 To UBound(nameArray)
        If Left(Trim(nameArray(arrCounter)), Len(searchname)) = searchname Then
            findPercent = Trim(Right(Trim(nameArray(arrCounter)), Len(Trim(nameArray(arrCounter))) - Len(searchname)))
            Exit For
        End If
    Next
End Function

In the end, storing data in comma delimited fields is just terrible design, but if you don't have control over the design of the data, then this UDF might be your best bet.

Stick that in a new module and then you can use it in your spreadsheet like:

=findPercent("Steve", A1)

Where A1 is your cell that has these comma separated name/percents.