Splitting Text from special characters 3 times

[dan1992]

Hello,
I have a cell containing the following information:

"AM,1,10|LA,1,5|VU,1,5"

The end product i need is one column containg AM (so after" and before , ) one column containing LA (so between | | ) one column containing VU (after | and before ")
I need this to be a formula as the codes used in the example with change and some of the codes are only 1 letter.
Any help would be hugely appreciated.
Hope that made sense

 

[sandy666]

use Power Query with Split (Before, After or Between) delimiter

 

[dan1992]

use Power Query with Split (Before, After or Between) delimiter

Thanks for the swift reply - i would prefer a formula so it can stay in the spreadsheet and will auto updated when new data is input

 

[sandy666]

and will auto updated when new data is input

as well Power Query but if you prefer formula, your choice. Have a nice day

 

[dan1992]

as well Power Query but if you prefer formula, your choice. Have a nice day

I don't have power query?

 

[sandy666]

I don't have power query?

you've Power Query (Get&Transform) because you are on Office 365

 

[dan1992]

I cant find it - do you know the formula(s) that would be needed?

 

[Bo_Ry]

Text in A1
Please try at B1:D1
=TRIM(LEFT(SUBSTITUTE(MID(SUBSTITUTE(SUBSTITUTE($A1,"""",),"|",REPT(" ",30)),COLUMNS($B1:B1)*30-29,30),",",REPT(" ",30)),30))

 

[sandy666]

I don't know formula(s), maybe someone else

 

[dan1992]

Text in A1
Please try at B1:D1
=TRIM(LEFT(SUBSTITUTE(MID(SUBSTITUTE(SUBSTITUTE($A1,"""",),"|",REPT(" ",30)),COLUMNS($B1:B1)*30-29,30),",",REPT(" ",30)),30))

Thanks - I have put that formula in the cell next to the data and it has returned AM so the first part. Do i need to use similar formulas in the adjacent columns to get the middle and last letters?