Get Substring from a cell with variable length string

[azamo]

Hello Excelperts,

I find myself in a bit of a conundrum and looking for your help and expertise to learn how to pull a substring from a cell of variable length string.

From the transcript column A1, I have variable length transcript string, what I am looking to extract is the name of the person who is chatting with (highlighted in Red below). The text highlighted in GREEN is always consistent, so I need to pull the substring after the "You are now chatting with " and before the Period at the end of that line.

How would I go about doing this? Any help would be greatly appreciated!!

[TABLE="width: 500"]
<tbody>[TR]
[TD]Transcript[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]14:09:54 info: Thank you for choosing to chat with us. An agent will be with you shortly.


14:09:56 info:


14:09:56 info:


14:09:56 info: You are now chatting with Jose C.


14:10:10 Jose: <span>Hi, I'm Jose C, I am glad to help you.
[/TD]
[TD]Jose C[/TD]
[/TR]
</tbody>[/TABLE]

 

[mrshl9898]

How's this? I am assuming there is always a full stop after the name in this.

=MID(INDEX(A:A,MATCH("*You are now chatting with*",A:A,0),1),FIND("You are now chatting with",INDEX(A:A,MATCH("*You are now chatting with*",A:A,0),1))+25,LEN(INDEX(A:A,MATCH("*You are now chatting with*",A:A,0),1))-FIND("You are now chatting with",INDEX(A:A,MATCH("*You are now chatting with*",A:A,0),1))-25)

 

[footoo]

Another way - Assuming the string to be returned doesn't exceed 50 characters :

=IFERROR(SUBSTITUTE(MID(A2,FIND("You are now chatting with",A2)+26,50),".",""),"")

Enter in B2 and fill down

 

[azamo]

Another way - Assuming the string to be returned doesn't exceed 50 characters :

=IFERROR(SUBSTITUTE(MID(A2,FIND("You are now chatting with",A2)+26,50),".",""),"")

Enter in B2 and fill down

Thank you so much!!! you are amazing, this worked just like I expected.

 

[azamo]

Actually, it did not work as expected, I did not expand the row, my apologies!! there is still text after "Jose C.", How do I delete everything after the name?

 

[mrshl9898]

Do they always have just a single initial Surname?

=IFERROR(LEFT(SUBSTITUTE(MID(A2,FIND("You are now chatting with",A2)+26,50),".",""),FIND(" ",SUBSTITUTE(MID(A2,FIND("You are now chatting with",A2)+26,50),".",""))+1),"")

Or if there's always a full stop after the name

=IFERROR(LEFT(MID(A2,FIND("You are now chatting with",A2)+26,50),FIND(".",MID(A2,FIND("You are now chatting with",A2)+26,50))-1),"")