Multiple Values in single cells - finding values within

[spooonmanz]

Hi,
Apologize in advance for I expect this has been asked before but after searching the posts, I was unable to find a solution.

I have a report where one column of cells contain multiple values separated by a comma. The values between each comma may vary in length of 7-8 char and the # of values in each cell may vary as well. What I desire to do is to identify the last value and depending on it's value, possibly the 2nd and or 3rd last values in the string.

For example,
Cell S1 may have: A10-00,A100-00,A200-00,A500-00,A500-10
Cell S2 may have: A10-00,A10-01,A500-02
Cell S15000 may have: A100-10,A200-00,A300-01,A300-02,A350-03,A400-00,A402-20,A500-00,A500-22,A500-30

There are possible scenarios of values I may be looking for but typically I'm looking for the last value in the string or 2nd to last and last value.
In a rare instance, I may be interested in the 3rd or 4th last values.

For example using the above example data,
in Cell S1, I would like to collect the last three values (A200-00,A500-00,A500-10)
in cell S2, want to collect the last 2 values of A10-01, A500-02
in cell s15000, want to collect A402-20,A500-00,A500-22,A500-30

I can expand the column with text-to-columns. The challenge is the column S data may have 2-3 values comma separated or 20-30 values that are comma separated in the same cell. There are fields to the left in columns A-R, and T-V. I don't see how expanding the values makes this better or worse. However, I'm open to any solution.

I've been stumped most of today. I found the following on the web (possibly in this forum, don't recall) and it returns the last value but unsure how to find the 2nd, 3rd or 4th last values.....
=RIGHT(S2,LEN(S2)-FIND(CHAR(1),SUBSTITUTE(S2,",",CHAR(1),LEN(S2)-LEN(SUBSTITUTE(S2,",",""))))

I inserted a column (T) to house the formula and then copy/paste down the table to find the last value.

I hope this makes sense. Short of adding a file which I can do if that would be helpful.
Thank you in advance for any and all assistance,

PE

 

[Andrew Poulsom]

Welcome to MrExcel.

Would a VBA user defined function be acceptable?

 

[spooonmanz]

Yes with a little insight as to how to implement would be appreciated. It's been some time since I used one.

 

[Andrew Poulsom]

Try this:

Function GetItem(AllText As String, Optional Item As Variant)
    Dim ArrItems As Variant
    ArrItems = Split(AllText, ",")
    If IsMissing(Item) Then Item = UBound(ArrItems)
    GetItem = ArrItems(Item)
End Function

Insert a module into your workbook and paste the function into it. To return the last item you can use:

=GetItem(S1)

and to return the third item:

=GetItem(S1,3)

 

[spooonmanz]

Hi,
This is phenomenal! Unfortunately, the 'GETITEM' UDF returns the second value from the beginning rather than the first value from the end. I'm wondering if there is a reverse sort or reverse arrange function.

Thank you again for the quick responses and assistance.

PE

 

[Andrew Poulsom]

How about?

Function GetItem(AllText As String, Optional Item As Variant)
    Dim ArrItems As Variant
    ArrItems = Split(AllText, ",")
    If IsMissing(Item) Then Item = 0
    GetItem = ArrItems(UBound(ArrItems) - Item)
End Function

 

[spooonmanz]

That is cool! Thanks so much!

There was one minor detail that I changed. When I use "GetItem(r2,1)", it would return the 2nd to last item in the cell. Entering "GetItem(r2,0)" returned the first item to the right. I added "+ 1" so it reads:

GetItem = ArrItems(UBound(ArrItems) - Item + 1)

I have so many other questions but this solves my question. Best to leave those questions for another day.

Thanks again!