In this case, you want to split the string and then pivot the data, so that each of your assets is on rows rather than columns. Tableau works better with data that's 'tall' rather than 'wide'.
Unfortunately, in Tableau's data editor, you can split strings, but you can't then pivot on the calculations. You might be able to do this in Tableau Prep.
If you don't want to modify the original Excel file, the other option would be to join to a dummy file with X columns with the numbers 1, 2, 3, 4, etc. (up to the max number of assets). Pivot on that (so that each row becomes 4, with a 1, 2, 3, or 4 in it). Then use the number to extract the appropriate asset from the concatenated string.
As Branden mentioned, splitting and pivoting would be a great approach. Tableau Prep could indeed do this and if it is an option, I would highly consider it.
The suggestion of a dummy file is great, though I would probably just start with everything as rows and avoid having to pivot. Then you can join those together. So it would be something like this:
Do a cross database join on a calculated join of 1 = 1 so that every Project now has 4 rows:
Then, you can do the split and then return the correct Asset based on the row of data:
As you have extra rows now, you'll also have to deal with the fact that that inflates the value of your Budget and Value values. One possibility is to use a FIXED LOD expression like:
That should get you what you described, though I'm aware you said it was "oversimplified" so there may be some complications to the answer based on specific requirements.
Hope that helps!