Calculated column to split metadata

Question

Hopefully this is in the realms of the possible. I have a managed metadata column. I wanted this to allow multiple selections and through a workflow I've copied the column to another for use in filtering.

This of course brings in all the crud. So my clothing column turns into:

Clothing|caf8a8d2-979a-4006-808a-b5f20aceaff0

I then use a calculated column to remove the "pipe" character:

=LEFT([Meta_Duplicate],INT(FIND("|",[Meta_Duplicate])-1))

Which leaves me just with Clothing. Great. Unfortunately when using multiple selections it breaks. If I chose more than one and get this:

Clothing|caf8a8d2-979a-4006-808a-b5f20aceaff0;Equipment|bf567d89-6134-4380-9be6-c2543487962c

My formula just keeps the Clothing part. Is there a way of this formula removing the | and keeping my metadata? So it turns this:

Clothing|caf8a8d2-979a-4006-808a-5f20aceaff0;Equipment|bf567d89-6134-4380-9be6-c2543487962c

Into this:

Clothing;Equipment

Answer 1

I had to extract data from a metadata field before, although mine was date data.

I used a Sharepoint Designer workflow to extract a value from the date field and to copy it to a new field. Maybe my post could help you or perhaps spark an idea as to how you can solve your problem; have a look at the picture of the workflow, the "copy 6 characters from the start..." would copy the Clothing value. Then, depending on whether the number of characters in your string varies (ideally it would be a constant), you might be able to extract the Equipment value as well - see the Action in the workflow "starting at 0"...

You might find that the order of your workflow would need you to extract (copy and paste the value 'Equipment' to another field) first of all, since this value is in a trickier position 'mid-string'. Once Equipment has been split off, you could then make the workflow copy the easier 'Clothing' value, since this is at the start of your string. Otherwise I fear the "starting at" Action would fail - since Clothing is 8 characters, if you have a longer or shorter value the workflow would copy the alpha-numeric jumble instead.

EDIT: additional link for your research - although you'd need to test if this would work for a meta-data field type.