Extract a word in a string based on a ","

[gmazza76]

Good morning,

I have been looking for a calculation to help me pull a word/phrase from a string based on ",".
I don't want to auto delimit these when loading as there is an amount of data in the string I don't need, but the info is set out the same for each line.

I want to pull data between the 2nd & 3rd , but I will then want to pull from between others as I progress, but for the life of me I cant get anything to pull through.

I am currently using the code below, apologies if this is a simple one but it is driving me nuts

=TRIM(LEFT(SUBSTITUTE(MID(TabName[Column1],FIND(",",SUBSTITUTE(TabName[Column1],":",",",2))+1,LEN(TabName[Column1])),",",REPT(" ",LEN(TabName[Column1]))),LEN(TabName[Column1])))

String is
CS:20210412000000514,00001535020000034111,13835049,XXXX,Area,Brand,MODEL,,,,BoughtDate,,;

thanks in advance
Gavin

 

[Sufiyan97]

Good morning,

I have been looking for a calculation to help me pull a word/phrase from a string based on ",".
I don't want to auto delimit these when loading as there is an amount of data in the string I don't need, but the info is set out the same for each line.

I want to pull data between the 2nd & 3rd , but I will then want to pull from between others as I progress, but for the life of me I cant get anything to pull through.

I am currently using the code below, apologies if this is a simple one but it is driving me nuts

=TRIM(LEFT(SUBSTITUTE(MID(TabName[Column1],FIND(",",SUBSTITUTE(TabName[Column1],":",",",2))+1,LEN(TabName[Column1])),",",REPT(" ",LEN(TabName[Column1]))),LEN(TabName[Column1])))

String is
CS:20210412000000514,00001535020000034111,13835049,XXXX,Area,Brand,MODEL,,,,BoughtDate,,;

thanks in advance
Gavin

do you want extract text between 2nd and 3rd "," if I am correct use below
assuming your string starts from A2

=TRIM(LEFT(SUBSTITUTE(MID(SUBSTITUTE(A2,",","x",2),FIND("x",SUBSTITUTE(A2,",","x",2))+1,99),",",REPT(" ",99)),99))

 

[gmazza76]

Thanks for this. I am using PowerPivot.
What does the "x" stand for?

 

[gmazza76]

Thanks again , this does work I have amended to suite.

 

[Sufiyan97]

Thanks again , this does work I have amended to suite.

You're Welcome
glad to help

What does the "x" stand for?

First we SUBSTITUTE 2nd "," with x to find 2nd "," that is why "x" is used

 

[gmazza76]

@A Durfani, whats the best way to see if a word appears in the string. ie if "Mondeo" appears can I get my column Model to show Mondeo for that line of data

 

[Sufiyan97]

I think you should start a new thread for that with sample data