Formula help

The essence of lookup is a look-up value, for example a name. This value must be in a column of its own in a the look-up table. And it must be unique in that table because if it isn't the correctness of any lookup can't be assured. Therefore you will often see numbers being used instead of names. They are easier to assure uniqueness of.

The look-up value must also appear in the target table. It's more efficient to have it in it's own column here, too.

Now you are able to request a formula, for any cell in the target table that isn't in the column containing the look-up value, to say, "Take the look-up value in this row, find the matching row in the look-up table and return the value found in another column of that row." Excel has many functions to do this. The most popular one is VLOOKUP().

You may not have the power to determine the structure of the look-up table but your question allows me to think that you might actually be trying to give it structure, that you aren't in fact trying to look up anything but simply split the information presently in one column into several. That would be a good decision whatever your final goal.

That is because in order to look up a partial string that string must be parsed. You can do that once for all and re-use the result (split the information in your look-up column) or you can do it just for a single use, just to extract one item of information once. The action is the same and it can only be performed having exact knowledge of the generic composition of the look-up string.

Please rephrase your question (look for the Edit button to modify) to provide exact detail about the string to be split and add the info about the target table your question now lacks.