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.

Can you just use Data>>Text-To-Columns and set Comma as the seperator so you can `hlookup` or `vlookup` properly? — JNevill, Oct 15 '15 at 19:55

score 0 · Answer 1 · answered Oct 15 '15 at 20:10

0

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

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

answered Oct 15 '15 at 20:10

pnuts

58,317
11
87
139

score 0 · Answer 2 · answered Oct 15 '15 at 20:10

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.

Looking up multiple values from a single cell

2 Answers2