Google Sheets: Concat text to username in each cell but sometimes there's more than 1 username per cell

I have a spreadsheet with 20+ users working on it. There is a list of usernames and my goal is to concatenate text to hyperlink the username in markdown format so that the username can be clickable in our internal dashboard. The issue is that the usernames are not consistent since sometimes there is @ symbol before the username, after the username, or no @ symbol at all.

For example: In cell A3 [kenle@, @johnsmith], I want it to say

[kenle] (https://example.com/u/kenle), [johnsmith](https://example.com/u/johnsmith)

Currently I have tried the following formula but it won't work if there's more than 1 username in a cell:

=CONCATENATE("[",REGEXREPLACE(A2, "[@]", ""),"]","(https://example.com/u/",REGEXREPLACE(A2, "[@]", ""),")")

Example Sheet

Solution

You may try:

=map(A2:A,lambda(Σ,if(Σ="",,join(", ",map(index(trim(split(substitute(Σ,"@",),","))),lambda(Λ,"["&Λ&"](https://example.com/u/"&Λ&")"))))))