Long story short, my large playlist in my music data base got messed up during a sync.
I have a good m3u file but I need to change the path of each file to match the database locations.
So I would love to use a formula or vb to do the following:
Remove the first 3 characters after the third ""
Replace the first " - " with "" and ignore the second " - "
Yes there are spaces either side of the dash.
Essentially, I need this
F:\Music\AC-DC - Back In Black\08 AC-DC - Have A Drink On Me.mp3
To end up like this
F:\Music\AC-DC\Back In Black\AC-DC - Have A Drink On Me.mp3
I would do it manually...and may end up doing it manually, but there are ~1800 rows....so I would rather not.
Thanks for any help.
Hello!
The description you've given and the example shown don't match...
Remove the first three characters after the third ""
That is ok.
Replace the first " - " with "" and ignore the second
The example given actually
replaces the first " - " with "
\" (a backslash character)
F:\Music\AC-DC
- Back In Black\08 AC-DC - Have A Drink On Me.mp3
F:\Music\AC-DC
\Back In Black\AC-DC - Have A Drink On Me.mp3
Assuming this is what you wanted, and the data is in cell A1
Put in cell B1:
=REPLACE(LEFT(A1,FIND("#",SUBSTITUTE(A1,"","#",3))) & RIGHT(A1,LEN(A1)-FIND("#",SUBSTITUTE(A1,"","#",3))-3),SEARCH(" - ",LEFT(A1,FIND("#",SUBSTITUTE(A1,"","#",3))) & RIGHT(A1,LEN(A1)-FIND("#",SUBSTITUTE(A1,"","#",3))-3)),3,"")
and copy down.
Excel 2010
| A | B |
|---|
| F:\Music\AC-DC - Back In Black\08 AC-DC - Have A Drink On Me.mp3 | F:\Music\AC-DC\Back In Black\AC-DC - Have A Drink On Me.mp3 | |
<tbody>
[TD="align: center"]1[/TD]
</tbody>
[TABLE="width: 85%"]
<tbody>[TR]
[TD]
Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]B1[/TH]
[TD="align: left"]=REPLACE(
LEFT(A1,FIND("#",SUBSTITUTE(A1,"","#",3))) & RIGHT(A1,LEN(A1)-FIND("#",SUBSTITUTE(A1,"","#",3))-3),SEARCH(" - ",LEFT(A1,FIND("#",SUBSTITUTE(A1,"","#",3))) & RIGHT(A1,LEN(A1)-FIND("#",SUBSTITUTE(A1,"","#",3))-3)),3,"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Just in case you didn't want that backlash character to replace the first occurrence of " - "
Put whatever you want to place it in cell C1 (and copy this down as well, or change to an Absolute cell reference)
Excel 2010
| A | B | C |
|---|
| F:\Music\AC-DC - Back In Black\08 AC-DC - Have A Drink On Me.mp3 | F:\Music\AC-DC@Back In Black\AC-DC - Have A Drink On Me.mp3 | @ | |
<tbody>
[TD="align: center"]1[/TD]
</tbody>
[TABLE="width: 85%"]
<tbody>[TR]
[TD]
Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B1[/TH]
[TD="align: left"]=REPLACE(
LEFT(A1,FIND("#",SUBSTITUTE(A1,"","#",3))) & RIGHT(A1,LEN(A1)-FIND("#",SUBSTITUTE(A1,"","#",3))-3),SEARCH(" - ",LEFT(A1,FIND("#",SUBSTITUTE(A1,"","#",3))) & RIGHT(A1,LEN(A1)-FIND("#",SUBSTITUTE(A1,"","#",3))-3)),3,C1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
