Lookup for Comma-separated values with numerical suffixes

[100]

Hi there, I was wondering if would be possible to use a cell with comma-separated values that have suffixes (i.e. iterations or repetitions) that in turn translate into the full value in the lookup table. The dataset we use is frankly ugly, but it would be nice if there was a way to make the process easier as at the moment, I have a formula that strips all the numbers from the data and then does an TEXTJOIN(IF(ISNUMBER(SEARCH())) resulting in the full values, but not the numerical suffixes.

Data	Desired Result		Value	Full Value
APP,BAN1	Apple, Banana		APP	Apple
ORA1,ORA2	Orange, Orange 2		BAN	Banana
APP,ORA1,BAN2	Apple, Orange, Banana 2		ORA	Orange

Values without a numerical suffix or a suffix of 1 have the same behavior, whilst values greater than 1 result in their suffixes being included at the end of their full value. I am unfortunately unable to use VBS in our workflow as our work is done using Office 365 on the Web.

I greatly appreciate the help!

 

[mohadin]

Hi
and welcome
For The layout

Book1
	A	B	C	D	E
1	Data	Data		Value	Full Value
2	APP,BAN1	Apple,Banana1		APP	Apple
3	ORA1,ORA2	Orange1,Orange2		BAN	Banana
4	APP,ORA1,BAN2	Apple,Orange1,Banana2		ORA	Orange
Sheet1

Try

Sub test()
    Dim a, b, x, c
    Dim i&, ii&
    b = Cells(2, 4).CurrentRegion.Columns(1)
    c = Cells(2, 4).CurrentRegion.Columns(2)
    a = Cells(1, 1).CurrentRegion.Columns(1)
    For i = 2 To UBound(a)
        x = Split(a(i, 1), ",")
        For ii = 0 To UBound(x)
        If Len(x(ii)) <> 3 Then
            a(i, 1) = Replace(a(i, 1), x(ii), c(Application.Match(Left(x(ii), 3), b, 0), 1) & Right(x(ii), 1))
        Else
            a(i, 1) = Replace(a(i, 1), x(ii), " " & c(Application.Match(Left(x(ii), 3), b, 0), 1))
        End If
        a(i, 1) = Join(Split(a(i, 1), ","), ", ")
        Next
    Next
    Cells(1, 2).Resize(UBound(a)) = a
End Sub

 

[100]

Hi
and welcome
For The layout

Book1
	A	B	C	D	E
1	Data	Data		Value	Full Value
2	APP,BAN1	Apple,Banana1		APP	Apple
3	ORA1,ORA2	Orange1,Orange2		BAN	Banana
4	APP,ORA1,BAN2	Apple,Orange1,Banana2		ORA	Orange
Sheet1

Try

Sub test()
    Dim a, b, x, c
    Dim i&, ii&
    b = Cells(2, 4).CurrentRegion.Columns(1)
    c = Cells(2, 4).CurrentRegion.Columns(2)
    a = Cells(1, 1).CurrentRegion.Columns(1)
    For i = 2 To UBound(a)
        x = Split(a(i, 1), ",")
        For ii = 0 To UBound(x)
        If Len(x(ii)) <> 3 Then
            a(i, 1) = Replace(a(i, 1), x(ii), c(Application.Match(Left(x(ii), 3), b, 0), 1) & Right(x(ii), 1))
        Else
            a(i, 1) = Replace(a(i, 1), x(ii), " " & c(Application.Match(Left(x(ii), 3), b, 0), 1))
        End If
        a(i, 1) = Join(Split(a(i, 1), ","), ", ")
        Next
    Next
    Cells(1, 2).Resize(UBound(a)) = a
End Sub

Hi, thanks for the reply.

Unfortunately, we are unable to use VBS for our workflow (Excel for Web) and we have differing lengths for each value - although the farthermost numerical value (i.e. if value is ORAN1, lookup for ORAN and the end value '1') is always identical and what I need. At the moment I have a formula like this for the table you posted:

=TEXTJOIN(", ",1,IF(ISNUMBER(FIND(","&D2:D4&",",","&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,""),0,"")&",")),E2:E4, ""))

Although it's ugly, it does do the work of eliminating the numerical values and indexing the value that I am looking for. Being able to add those numerical suffixes at the end however would make the whole workflow a lot easier though.

 

[100]

Hi, thanks for the reply.

Unfortunately, we are unable to use VBS for our workflow (Excel for Web) and we have differing lengths for each value - although the farthermost numerical value (i.e. if value is ORAN1, lookup for ORAN and the end value '1') is always identical and what I need. At the moment I have a formula like this for the table you posted:
=TEXTJOIN(", ",1,IF(ISNUMBER(FIND(","&D2:D4&",",","&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,""),0,"")&",")),E2:E4, ""))

Although it's ugly, it does do the work of eliminating the numerical values and indexing the value that I am looking for. Being able to add those numerical suffixes at the end however would make the whole workflow a lot easier though.

VBA* - apologies for the typo in the post.

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E
1	Data	Data		Value	Full Value
2	APP,BAN1	Apple,Banana		APP	Apple
3	ORA1,ORA2	Orange,Orange		BAN	Banana
4	APP,ORA1,BAN2	Apple,Orange,Banana		ORA	Orange
Main

Cell Formulas
Range	Formula
B2:B4	B2	=TEXTJOIN(",",,VLOOKUP(LEFT(TEXTSPLIT(A2,","),3),$D$2:$E$4,2,0))

 

[Fluff]

Another option to return the numbers

=LET(t,TEXTSPLIT(SUBSTITUTE(A2,"1",""),","),TEXTJOIN(",",,VLOOKUP(LEFT(t,3),$D$2:$E$4,2,0)&MID(t,4,4)))

 

[100]

Another option to return the numbers

=LET(t,TEXTSPLIT(SUBSTITUTE(A2,"1",""),","),TEXTJOIN(",",,VLOOKUP(LEFT(t,3),$D$2:$E$4,2,0)&MID(t,4,4)))

Thank you for the reply! Unfortunately some of the values I have are more than 3 characters long (e.g., APP, BANA, ORAN) which is why I went with the route of bruteforcing the numbers out and then using a lookup. If I also can't have the suffixes for the values displayed with the suffixes (for example APP2, BANA2, ORAN3 resulting in Apple 2, Banana 2, Orange 3) the formula I have in #3 is what I'll have to stick with for now.

 

[Fluff]

Are all the numbers only one digit long?

 

[100]

Are all the numbers only one digit long?

Some values are stylized in a way like 1, 2, and some 01, 02, but the furthermost numerical value is the only thing that I need. A more detailed example as my initial one was lacking (apologies):

Data	Desired Result		Value	Full Value
AP01,ORAN2,BAN1	Apple (1), Orange 2, Banana (1)		AP	Apple
APRI,BAN2	Apricot, Banana (2)		ORAN	Orange
			BAN	Banana
			APRI	Apricot

 

[Fluff]

How about

=LET(t,TEXTSPLIT(SUBSTITUTE(A2,"0",""),","),b,ISNUMBER(RIGHT(t)+0),TEXTJOIN(",",,VLOOKUP(LEFT(t,LEN(t)-b),$D$2:$E$5,2,0)&MID(t,LEN(t)+1-b,4)))