0

enter image description hereI have a list of instagram url's in a excel sheet and i need to extract their user names into another column using a formula.

I have used this formula but it's incomplete and does not work as i need.

=RIGHT(C111,FIND("/",C111))

For example, how can i extract the user name stack_test from the url https://www.instagram.com/stack_test/ which is in a excel sheet.

CraZyDroiD
  • 6,622
  • 30
  • 95
  • 182

4 Answers4

1

If you need user names only then you can do it without formula. You can use Text to Column (Short Cut:Alt + D then E). Select Delimited, In delemeters dialog select Other and use /.

Adam Thompson
  • 21
  • 5
1

EDIT FILTERXML XPATH argument changed. After doing some research, discovered you can specify "next to last" node, instead of using the LOOKUP function.

If your version of Excel has the FILTERXML function, you can use:

=FILTERXML("<t><s>"&SUBSTITUTE(A1,"/","</s><s>")&"</s></t>","//s[last()-1]")

If you have an older version of Excel, you can use:

=TRIM(RIGHT(SUBSTITUTE(IF(RIGHT(A1,1)="/",LEFT(A1,LEN(A1)-1),A1),"/",REPT(" ",99)),99))
Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
0

Try:

=MID(A1,FIND("com/",A1)+4,(SEARCH("^^",SUBSTITUTE(A1,"/","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))))-(FIND("com/",A1)+4)))
Error 1004
  • 7,877
  • 3
  • 23
  • 46
  • Formula should be written to extract the word between / and / from the end of the url – CraZyDroiD Apr 09 '19 at 09:16
  • The proposed answer is just a guessing. Please provide a sample of your data. Url has a lot of "/" in it. what exactly you want to extract. – Error 1004 Apr 09 '19 at 09:17
  • I have added a screen shot of my excel sheet. What i need to do is extract the usernames of the given instagram links to D column. For example just ASSUME there's a url like this , https://www.instagram.com/stack_test/. I want to extract stack_test part of the url – CraZyDroiD Apr 09 '19 at 09:22
  • Hey Thanks alot.It's working. Just one thing. How can i remove the last " / " character? – CraZyDroiD Apr 09 '19 at 09:27
  • i have change the naswer. – Error 1004 Apr 09 '19 at 09:41
0

This works if there are exactly two / in each row, and the username is between them.

Type some_text/username/some_other_text in cell A1.

I broke down the formula in multiple cells to make it more readable, but you can make a one-cell formula:

B1: =FIND("/",A1)

C1: =FIND("/",A1,B1+1)

D1: =MID(A1,B1+1,C1-B1-1) . This will show username

If there can be multiple / and you only need the last two, it is more complicated. You might want to have a look:

Pythonista anonymous
  • 8,140
  • 20
  • 70
  • 112