I have a table of ids and need to be able to have two separate columns one with the name and one with the version. The issue I am having is that the version numbers have a varying number of characters. Also, there are numerical characters in the name. I have tried many various formulas but only getting the digits after the last dash.Can someone help, please? I have tried to ut the version numbers in bold

This is how I need my table to look:

I have a table of ids and need to be able to have two separate columns one with the name and one with the version. The issue I am having is that the version numbers have a varying number of characters. Also, there are numerical characters in the name. I have tried many various formulas but only getting the digits after the last dash.Can someone help, please? I have tried to ut the version numbers in bold

This is how I need my table to look:

I have tried this formula to extract the version number:
=IF(ISNUMBER(--RIGHT(A2, 1)), TRIM(RIGHT(SUBSTITUTE(A2, "-", REPT(" ", LEN(A2))), FIND("^", SUBSTITUTE(SUBSTITUTE(A2, "-", "^", LEN(A2) - LEN(SUBSTITUTE(A2, "-", "")) - 2), "-", "^", LEN(A2) - LEN(SUBSTITUTE(A2, "-", "")) - 1)))), "(No version)")

and then this formula to give just the app name:
=IF(B2="(No version)", A2, LEFT(A2, LEN(A2) - LEN(B2) - 1))

and it gives this result:

So it appears that it's just giving me the characters after the last dash but I don't know how to solve as it's not a consistent number of characters

• excel
• excel-formula
• excel-365

• Perhaps this formula gives you ideas? =TEXTJOIN("-",TRUE,LET(items,TEXTSPLIT(A1,,"-"),IF(ISERROR(items*1),"",items))) It doesn't handle the second and third rows of your sample data very well though. – jkpieterse Commented 2 days ago
• It also picks out the numbers in rows 4 & 5 which are in the middle of the name and not version numbers – Kelly Ackary Commented 2 days ago
• I knew that formula didn't do exactly what you needed, but I thought it'd be worthwhile to share as a starting point. – jkpieterse Commented yesterday

4 Answers 4

Here is one way to accomplish the desired output:

=MAP(A2:A10,LAMBDA(x,
 LET(a, TEXTSPLIT(x,"-"), 
     b, ISERR(--a), 
     IFERROR(TEXTJOIN("-",,TOROW(--TAKE(a,,
             -(COLUMNS(b)-SUM(--b))),2)),"(No Version)"))))

While for the Ref Id, could base the version output column, substitute it to get the ref id:

=MAP(A2:A10,B2:B10,LAMBDA(x,y, SUBSTITUTE(x,"-"&y,)))

And to get one single dynamic array output, you could try the following as well:

=DROP(REDUCE("",A2:A10,LAMBDA(x,y,
 VSTACK(x, LET(a, TEXTSPLIT(y,"-"),
               b, ISERR(--a),
               c, IFERROR(TEXTJOIN("-",,TOROW(--TAKE(a,,
                         -(COLUMNS(b)-SUM(--b))),2)),"(No Version)"),
               HSTACK(c, SUBSTITUTE(y,"-"&c,)))))),1)

It can be a solution:

=LET(lam,LAMBDA(a,n,fn,IF(CODE(RIGHT(a,n))>57,n,fn(a,n+1,fn))),
inp,A2:A10,x,MAP(inp,LAMBDA(v,lam(v,1,lam))),
HSTACK(IFERROR(RIGHT(inp,x-2),"(No version)"),LEFT(inp,LEN(inp)-x+1)))

'lam' scans the text backward to find the char after (in ASCII order) '9'.

'x' is the array of positions found.

Another variant is with regular expression functions:

=LET(s,A2:A10,r,"[0-9-]+$",
HSTACK(IFERROR(MID(REGEXEXTRACT(s,r),2,255),"(No version)"),REGEXREPLACE(s,r,"")))

For Version Number:

=LET(shatter, TEXTSPLIT(A2,"-"), 
    versionUnits, REDUCE(0, shatter, LAMBDA(a, v, IF(ISERROR(--v), 0, a+1))),
    IFERROR(TEXTJOIN("-", FALSE, TAKE(shatter, 1, -versionUnits)),
        "(No version)"
    )
)

• Breaks the Long Name into an array of values, delimited by hyphens, and calls this shatter
• Runs through each element in Shatter, adding 1 to the accumulator if the element is a number, and resetting the accumulator back to 0 if the element is not a number. This tells us how many elements the version number is, and stores it as versionUnits
• Take the last versionUnits elements from shatter, and join them together with hyphens
• If versionUnits is 0, then return "No version" instead

For Reference ID:

=LET(shatter, TEXTSPLIT(A2,"-"), 
    versionUnits, REDUCE(0, shatter, LAMBDA(a, v, IF(ISERROR(--v), 0, a+1))),
    IFERROR(TEXTJOIN("-", FALSE, DROP(shatter, 0, -versionUnits)),
        ""
    )
)

• Same process to calculate versionUnits
• Drops the last versionUnits elements from shatter, and then joins together everything that's left with hyphens
• If the entire Long Name is a version number, returns an empty string

Combined:

=LET(shatter, TEXTSPLIT(A2,"-"), 
    versionUnits, REDUCE(0, shatter, LAMBDA(a, v, IF(ISERROR(--v), 0, a+1))),
    HSTACK(IFERROR(TEXTJOIN("-", FALSE, TAKE(shatter, 1, -versionUnits)),
            "(No version)"
        ),
        IFERROR(TEXTJOIN("-", FALSE, DROP(shatter, 0, -versionUnits)),
            ""
        )
    )
)

• Does both of the above, spilling horizontally

This requires M365:

=LET(L,LAMBDA(x,MAP(A2:A10,LAMBDA(a,
                              LET(b,TEXTSPLIT(a,,"-"),
                                  c,XMATCH(TRUE,ISERR(--b),,-1),
IFERROR(TEXTJOIN("-",1,x(b,c)),
        "(no version)"))))),
HSTACK(L(DROP),
       L(TAKE)))

Lambda Function L is created to map A2:A10.

b splits each mapped value into an array and converts it to a number. If it's text that creates an error value.

c finds the position of the last found error value and is used to DROP or TAKE that instance of values from b and join these.

In case the last value of b is a text value, the DROP argument will drop all values of n, producing an error. This is handled by IFERROR, while take will take all, so returns the whole string.