Ignore file path in cell contents

**ianarman** · 06-20-2013, 10:11 PM

Hello,

I'm organizing my MP3 ID3 tags, using Tag&Rename (cool program). Anyway it allows for the importing / exporting of the ID3 tags, into a spreadsheet.

In a particular column the complete file path is displayed (see example below). I'm trying to get excel to ignore "C:\Users\Ian\Music\Karaoke\L\", so I can extract 'Livin' La Vida Loca - Ricky Martin.mp3'

C:\Users\Ian\Music\Karaoke\L\Livin' La Vida Loca - Ricky Martin.mp3

**Tony Valko** · 06-20-2013, 10:20 PM

Try this...

A2 = C:\Users\Ian\Music\Karaoke\L\Livin' La Vida Loca - Ricky Martin.mp3

Enter this formula in B2:

=TRIM(RIGHT(SUBSTITUTE(A2,"\",REPT(" ",255)),255))

**ianarman** · 06-20-2013, 10:27 PM

@Tony... "oh wow...."!

Could you please explain the equation? Oh wise one...

**ianarman** · 06-20-2013, 10:29 PM

it looks like function substituted the text between the slashes.... with a space, then the space for nothing?

if you wouldn't mind explaining a bit more.....

**Tony Valko** · 06-20-2013, 10:40 PM

It (virtually) replaces each slash \ with 255 space characters.

The underscores represent the space characters:

C:_____Users_____Ian_____Music_____Karaoke_____L_____Livin' La Vida Loca - Ricky Martin.mp3

Then it extracts the last 255 characters:

___Livin' La Vida Loca - Ricky Martin.mp3

And strips off the space characters:

Livin' La Vida Loca - Ricky Martin.mp3