Excel Is it possible to reorder the data in 2 columns to match up if they have a certain number of characters a string in common

0 votes

I'm now rearranging a sizable number of media files in an effort to make them adhere to a tracking system. I currently have two lists, one of which is a list of all the URLs from which the videos have been ripped.

The names of the.mp4 files from the bulk download are the other.

Column A looks like

C:\Users\XXX\BIN\MEDIA\1614561651289915393_2.mp4
C:\Users\XXX\BIN\MEDIA\1615390470523191305_2.mp4
C:\Users\XXX\BIN\MEDIA\1612041791171084288_2.mp4
C:\Users\XXX\BIN\MEDIA\1612425997130911747_2.mp4

Column B looks like:

https://twitter.com/user/status/1611717485828489221?s=12&t=HFVIWyICVNxavFltOixxVg
https://twitter.com/user/status/1612425997130911747?s=12&t=gU4QIpi1peMHASLbiRGKjA
https://twitter.com/user/status/1612831672805855232?s=12&t=IQ_M-PpP05ylmIRsBjlIfg
https://twitter.com/user/status/1604835107759853568?s=12&t=wzY0CTl6lyINeaFxisyZcQ
 

Column A should be rearranged such that entries correspond to the URLs that contain the filename between /status/ and?s=12&t.

Is that plausible or does it make sense?

Jan 21 in Others by Kithuzzz
• 34,760 points
75 views

1 answer to this question.

0 votes

Try this:

=LET(files,A1:A4,
URLs,B1:B4,
f,BYROW(files,LAMBDA(r,TEXTSPLIT(TAKE(TEXTSPLIT(r,"\"),,-1),"_"))),
XLOOKUP("*/" & f &"~?*",URLs,URLs,"not found",2))
answered Jan 21 by narikkadan
• 59,740 points

Related Questions In Others

0 votes
1 answer
0 votes
1 answer

Is it possible to round to different decimal places in excel based on the range?

Where the range is multiple cells: Excel.Worksheet sheet ...READ MORE

answered Nov 17, 2022 in Others by narikkadan
• 59,740 points
114 views
0 votes
1 answer

Excel formula to get certain cell if the value is between 2 numbers

So, first with vlookup(): A formula so you ...READ MORE

answered Feb 11 in Others by Kithuzzz
• 34,760 points
126 views
0 votes
0 answers

Excel - How do I round a date type to the next hour if it is more than one minute

So I have a hh:mm: ss date ...READ MORE

14 hours ago in Others by Kithuzzz
• 34,760 points
14 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,630 points
271 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,630 points
531 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,630 points
263 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE

answered Feb 18, 2022 in Others by gaurav
• 22,970 points
302 views
0 votes
1 answer

MAX function in Excel: is it possible to provide the range by means of variables?

Try this: =MAX(INDEX(A:A,B2):INDEX(A:A,B3)) READ MORE

answered Nov 15, 2022 in Others by narikkadan
• 59,740 points
90 views
0 votes
1 answer

Is it possible to get data from a webpage in real-time to an excel file?

The conventional method of obtaining data from ...READ MORE

answered Jan 17 in Others by narikkadan
• 59,740 points
72 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP