Split Text into 1 Column from String

[shaon814]

Hi guys,

I'm trying to extract the first and last name from the string below into a Cell.

[TABLE="width: 428"]
<tbody>[TR]
[TD]https://websitename.com/tour#/first-last?p=237[/TD]
[/TR]
</tbody>[/TABLE]

I tried the following function "=RIGHT(C2,LEN(C2)-SEARCH("bands/",C2,1)-5)", but only getting "first-last?p=237".

Is there a way to get only the First and Last name in the cell and not all the other characters?

-Thanks in advance!

 

[63falcondude]

I'm sure there is a better solution out there but here is what I came up with

=MID(SUBSTITUTE(C2,"/","|",4),FIND("|",SUBSTITUTE(C2,"/","|",4))+1,FIND("?",SUBSTITUTE(C2,"/","|",4))-FIND("|",SUBSTITUTE(C2,"/","|",4))-1)

 

[Bunny11]

I think this should work?

"=LEFT(C2;FIND(" ";C2)-1)&RIGHT(C2;FIND(" ";C2))"

 

[Bunny11]

i'm sorry i read the question wrong

 

[shaon814]

That definitely worked, thank you! Is there a way to get rid of the "-" in "first-last"

That'll probably make the equation longer/busier xD

I'm sure there is a better solution out there but here is what I came up with

=MID(SUBSTITUTE(C2,"/","|",4),FIND("|",SUBSTITUTE(C2,"/","|",4))+1,FIND("?",SUBSTITUTE(C2,"/","|",4))-FIND("|",SUBSTITUTE(C2,"/","|",4))-1)

 

[shaon814]

No problem, really appreciate it though! Still trying to figure it out using Right and Left functions.

i'm sorry i read the question wrong

 

[63falcondude]

That definitely worked, thank you! Is there a way to get rid of the "-" in "first-last"

That'll probably make the equation longer/busier xD

Sure

=SUBSTITUTE(original formula,"-"," ")

So it would look like this:

=SUBSTITUTE(MID(SUBSTITUTE(C2,"/","|",4),FIND("|",SUBSTITUTE(C2,"/","|",4))+1,FIND("?",SUBSTITUTE(C2,"/","|",4))-FIND("|",SUBSTITUTE(C2,"/","|",4))-1),"-"," ")

 

[Bunny11]

had to give it another try

=MID(C2;FIND("#/";C2)+2;FIND("?p";C2)-(FIND("#/";C2)+2))

 

[shaon814]

Just tried it, no luck =(

I also tried it with replacing the ; with , in the equation. I think there's an error with the syntax - not 100% sure though

=MID(C2,FIND("#/",C2)+2,FIND("?p",C2)-(FIND("#/",C2)+2))[/QUOTE]

had to give it another try

=MID(C2;FIND("#/";C2)+2;FIND("?p";C2)-(FIND("#/";C2)+2))

 

[Rick Rothstein]

I'm trying to extract the first and last name from the string below into a Cell.

[TABLE="width: 428"]
<tbody>[TR]
[TD]https://websitename.com/tour#/first-last?p=237[/TD]
[/TR]
</tbody>[/TABLE]

If you single example is truly representative of what your actual data could look like, then this formula will work...

=SUBSTITUTE(MID(LEFT(A1,FIND("?",A1)-1),FIND("#/",A1)+2,99),"-"," ")