Extracting variable length data from cell

[Sonia10]

Would like a formula which would extract the contents between the word circuit and the second comma in a string:

MPLS - MPLS Services, Circuit 2083676, Verizon, xxxx,,xxxxxxxxxxxxx , xx
MPLS - MPLS Services, Circuit 572222DAT1, xxxx,xxxxx,xxxxx

 

[Fluff]

Hi & welcome to MrExcel.
How about
=TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",100),2),SEARCH("circuit",A1)+8,100))

 

[Sonia10]

Thanks this worked for most but not:

Internet - Internet (DSL,Internet), Circuit XXXX,xxx,xxxx,xxx,xx

This entry has an additional ","

 

[Fluff]

It doesn't work for that, because you said after the 2nd comma
Do you have any other variations?

 

[tyija1995]

Here is a formula you can try that extracts text AFTER the word "circuit" and before the comma that follows after said word.

=TRIM(LEFT(SUBSTITUTE(MID(SUBSTITUTE(LOWER(A1),"circuit",REPT(" ",LEN(A1))),LEN(A1)+1,LEN(A1)),",",REPT(" ",LEN(A1))),LEN(A1)))

EDIT: Curse those case sensitivities!

 

[Sonia10]

Sorry - didn't notice it until I applied your formula. I think this is it.

 

[Phuoc]

Maybe like this?

=TRIM(LEFT(SUBSTITUTE(REPLACE(A1,1,SEARCH(" Circuit",A1)+8,""),",",REPT(" ",100)),100))

 

[Fluff]

Ok, another option
=MID(A1,SEARCH("circuit",A1)+8,SEARCH(",",A1,SEARCH("circuit",A1)+8)-(SEARCH("circuit",A1)+8))

 

[Sonia10]

Perfect! Thank you

 

[Fluff]

Glad we could help & thanks for the feedback