seperate a set of characters after the last semi colon, in a string of characters

I would like to seperate a set of characters, after the last semi colon, in a string of characters, within the same cell.

This is an example of the cell contents METALS>Ferrous>Carbon&Alloy:A500:Pipe Size Tub, A500-B, 50ksi:HC.18.000.0.500.238.500

I would like the formula to give me this as a result HC.18.000.0.500.238.500 which is the string of characters after the last semi colon.

Keep in mind, there may be only two semi colons in the string of characters or as many as 4 semi colons.
That's why I need the result based only on the string of characters after the last semi colon on the cell contents.

HC.18.000.0.500.238.500 is 23 Characters.

is this string always at the end?
and is it always 23 characters?

If this isn't the case
I think the easiest thing you can possibly do is use the "Text to Columns" feature
The character I am seeing in that string is a colon ( and not a semi-colon (

Data
Click "Text To Columns"
make sure "Delimited" is selected and choose "next"

Make sure the only "Delimiter" that is selected is "Other".
Put a : in the blank spot.
Hit Next then Finish.

Since ":" is now translated to a gridline the data you need will always be in the last cell of the original string.

Doesn't matter if it is 2 ":" or 4 ":"

We can do an "ifblank" formula from that point.

VBA would ne the route I would go if you want to automate this process.

In the end, it all depends on the variables.

Wow thank you very much!

Now I just have to figure out how the actual formula looks.

Attached is a portion of the file with a portion of the column I am working with.

I apologize, I found the "Data" tab and now I am following your instructions.

Thank you again !!!