I've got an .xl with a column full of media file names (e.g. "0020_519483_4_Elizabeth_K_846114_1.mp4").
I need a formula that will pull specific parts of the media file name and show it in another column, in a specific format ("A20 q4").
Here's how the data I want relates to the media file. I want the formula to convert "0020_519483_4_Elizabeth_K_846114_1.mp4"
into "A20 q4"
So, it's starting by adding an "A", followed by the 3rd and 4th digits of the filename ("0020_"). Then a space, then a "q" followed by the number between the 2nd and third "_" (in this case "4").
I'm currently using the formula below:
="A"&IF(MID(AH1398,3,1)="0",MID(AH1398,4,1),MID(AH1398,3,2)) & " " & "q" & MID(AH1398, FIND(CHAR(1),SUBSTITUTE(AH1398,"_",CHAR(1),2))+1, FIND(CHAR(1),SUBSTITUTE(AH1398,"_",CHAR(1),3)) - FIND(CHAR(1),SUBSTITUTE(AH1398,"_",CHAR(1),2))-1)
But it's not working right, and i can't figure out how to make it work.
Any help would be very much appreciated.
Bookmarks