Can you turn this excel formula into Numbers?

You can concatenate but Numbers has no function that applies text styles. If the text in cell "Menopiu" is formatted to look like what you want it to look like in this concatenation, that formatting will be applied in the formula. Given that your only other formatting is red text, format the cell with the formula as red text. There are limitations if you want a mix of bold, italics, etc.

Also, the cell named "Menopiu" will have to become a regular cell reference (like B2, C34, etc). You can use headers as cell names and get close to what you have I think. I don't used them.

Yes, you can concatenate cells of different formatting styles together into a string that exhibits all the styles of its components. I have a thread on it here: https://discussions.apple.com/thread/251342337

Here’s an example:

Here’s a template of that same example:

https://www.icloud.com/iclouddrive/0NTiysyOH87xg4kHh4dPTIpvQ#Blank_142

Two caveats:

1) the text that is to appear in red must already be present somewhere in your document so you can reference it, and it must be coloured red.

2) the cell in which the concatenated string is to populate must be formatted a specific way that may in fact be a glitch. I have never been able to replicate it consistently, so I just copy and paste it into any new sheet I create. You should be able to do the same from the template.

The formatting you’ll need to apply is that which exists in Table1::A1. Be aware that while this cell, and any cell in which this formatting is pasted, will exhibit the quality of retaining the formatting of any cell it references, this will cease to be true if you attempt to manually change the format in any way.