Remove text before last specified special character in Excel

Question

I've got a list of categories in a breadcrumb trail, and I need to extract the latest category.

Below is a sample of the list, and I need to get the category after the last /, or if there is no /, then the category. So the following would turn into the below.

Before

Sewing
Crafting/Paper & Paper Crafts
Scrapbooking & Stamping
Crafting/Craft Supplies
Painting, Drawing & Art Supplies/Painting/Brushes
Beading & Jewelry Making/Charms
Beading & Jewelry Making/Beading Supplies

After

Sewing
Paper & Paper Crafts
Scrapbooking & Stamping
Craft Supplies
Brushes
Charms
Beading Supplies

Can anyone point me to an excel formula that can do this please?

Answer 1

Use the SUBSTITUTE function to create a large gap of spaces at the forward slashes then peel off the right end and use the TRIM function to clean it up.

=TRIM(RIGHT(SUBSTITUTE(A1, "/", REPT(" ", 999)), 999))

Fill down as necessary.