I renamed all my digital and scanned photos but I have applications
that refers to the old names and directories.
I want to replace the old names & directories with the new names and
the new directories for all those jpg file .

On sheet 1, I have in col A, all the new directory & filename
and in Col B, all the old file name only (the old directory is not
there)
There are 1500 lines (pictures) that were renamed. (sorted on col B)

Col A Col B
E:\My PICTURES\2004\20040326_1715.jpg hawai.jpg
E:\My PICTURES\2004\20040326_1698.jpg Kat25.jpg
E:\My PICTURES\2004\20040326_1700.jpg LLLL.jpg
E:\My PICTURES\2004\20040508_1697.jpg L05_0592.JPG

On sheet 2, I copied one of the many txt files of an application where
we can find lines
that refers to the old directory and filename of a picture.

lines...
cell[2].images[0].image=../../../E:\My PICTURES\Fam/Friends\kat25_.jpg
lines...
cell[32].images[0].image=../../../E:\My PICTURES\Fam/Friends\Kito6.jpg
lines...
cell[151].images[0].image=../../../E:\MyPICTURES\Others/cats\IMG_0256.JPG
lines...

I want to replace this old directory and filename
E:/My Pictures/Fam/Friends/kat25_.jpg
with the new directory and filename;
E:\My Pictures\2004\20040326_1698.jpg
but also keep the information that preceded the old filename &
directory.

Final result on sheet 2 should be;
cell[2].images[0].image=../../../E:\My Pictures\2004/20040326_1698.jpg

Any suggestions on a possible solution?

Den

On Sun, 12 Mar 2006 11:50:42 -0500, Den wrote:

I renamed all my digital and scanned photos but I have applications
that refers to the old names and directories.
I want to replace the old names & directories with the new names and
the new directories for all those jpg file .

On sheet 1, I have in col A, all the new directory & filename
and in Col B, all the old file name only (the old directory is not
there)
There are 1500 lines (pictures) that were renamed. (sorted on col B)

Col A Col B
E:\My PICTURES\2004\20040326_1715.jpg hawai.jpg
E:\My PICTURES\2004\20040326_1698.jpg Kat25.jpg
E:\My PICTURES\2004\20040326_1700.jpg LLLL.jpg
E:\My PICTURES\2004\20040508_1697.jpg L05_0592.JPG

On sheet 2, I copied one of the many txt files of an application where
we can find lines
that refers to the old directory and filename of a picture.

lines...
cell[2].images[0].image=../../../E:\My PICTURES\Fam/Friends\kat25_.jpg
lines...
cell[32].images[0].image=../../../E:\My PICTURES\Fam/Friends\Kito6.jpg
lines...
cell[151].images[0].image=../../../E:\MyPICTURES\Others/cats\IMG_0256.JPG
lines...

I want to replace this old directory and filename
E:/My Pictures/Fam/Friends/kat25_.jpg
with the new directory and filename;
E:\My Pictures\2004\20040326_1698.jpg
but also keep the information that preceded the old filename &
directory.

Final result on sheet 2 should be;
cell[2].images[0].image=../../../E:\My Pictures\2004/20040326_1698.jpg

Any suggestions on a possible solution?

Den

First swap your two columns A & B on sheet 1 over so that the new file
name bcomes the subject value of a Vlookup. (You could probably use
offsets in the Formula below but I think that would unnecessarily
complicate it.

Then on Sheet 2, with your old folder/file names in A1:A..., put the
following in B1 and copy down.

=VLOOKUP((RIGHT(A1,MATCH("\",MID(A1,LEN(A1)-ROW(1:255),1),0))),Sheet1!A2:B5,2,FALSE)

This is an array formula, so enter with CTRL Shift and Enter

I assumed that there was a type above where you said that the second
file name in column B was Kat25.jpg, whereas your old line names
included Kat25_jpg - i.e. with an underscore character.

The '255' number is only there to catch all file names up to 255
characters in length.

HTH
Richard Buttrey
__

On Sun, 12 Mar 2006 18:01:10 +0000, Richard Buttrey
wrote:

On Sun, 12 Mar 2006 11:50:42 -0500, Den wrote:

I renamed all my digital and scanned photos but I have applications
that refers to the old names and directories.
I want to replace the old names & directories with the new names and
the new directories for all those jpg file .

On sheet 1, I have in col A, all the new directory & filename
and in Col B, all the old file name only (the old directory is not
there)
There are 1500 lines (pictures) that were renamed. (sorted on col B)

Col A Col B
E:\My PICTURES\2004\20040326_1715.jpg hawai.jpg
E:\My PICTURES\2004\20040326_1698.jpg Kat25.jpg
E:\My PICTURES\2004\20040326_1700.jpg LLLL.jpg
E:\My PICTURES\2004\20040508_1697.jpg L05_0592.JPG

On sheet 2, I copied one of the many txt files of an application where
we can find lines
that refers to the old directory and filename of a picture.

lines...
cell[2].images[0].image=../../../E:\My PICTURES\Fam/Friends\kat25_.jpg
lines...
cell[32].images[0].image=../../../E:\My PICTURES\Fam/Friends\Kito6.jpg
lines...
cell[151].images[0].image=../../../E:\MyPICTURES\Others/cats\IMG_0256.JPG
lines...

I want to replace this old directory and filename
E:/My Pictures/Fam/Friends/kat25_.jpg
with the new directory and filename;
E:\My Pictures\2004\20040326_1698.jpg
but also keep the information that preceded the old filename &
directory.

Final result on sheet 2 should be;
cell[2].images[0].image=../../../E:\My Pictures\2004/20040326_1698.jpg

Any suggestions on a possible solution?

Den

First swap your two columns A & B on sheet 1 over so that the new file
name bcomes the subject value of a Vlookup. (You could probably use
offsets in the Formula below but I think that would unnecessarily
complicate it.

Then on Sheet 2, with your old folder/file names in A1:A..., put the
following in B1 and copy down.

=VLOOKUP((RIGHT(A1,MATCH("\",MID(A1,LEN(A1)-ROW(1:255),1),0))),Sheet1!A2:B5,2,FALSE)

This is an array formula, so enter with CTRL Shift and Enter

I assumed that there was a type above where you said that the second
file name in column B was Kat25.jpg, whereas your old line names
included Kat25_jpg - i.e. with an underscore character.

The '255' number is only there to catch all file names up to 255
characters in length.

HTH
Richard Buttrey
__

Sorry, missed off the front bit which concatenates the
cell[2].images...... bit

This assumes there is only one "\" before the reference to the Drive
"E:" reference. If not please post back.

=LEFT(A1,FIND("\",A1)-3)&VLOOKUP((RIGHT(A1,MATCH("\",MID(A1,LEN(A1)-ROW(1:255),1),0))),Sheet1!A2:B5,2,FALSE)

Rgds


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

On Sun, 12 Mar 2006 18:11:55 +0000, Richard Buttrey
wrote:

On Sun, 12 Mar 2006 18:01:10 +0000, Richard Buttrey
wrote:

On Sun, 12 Mar 2006 11:50:42 -0500, Den wrote:

I renamed all my digital and scanned photos but I have applications
that refers to the old names and directories.
I want to replace the old names & directories with the new names and
the new directories for all those jpg file .

On sheet 1, I have in col A, all the new directory & filename
and in Col B, all the old file name only (the old directory is not
there)
There are 1500 lines (pictures) that were renamed. (sorted on col B)

Col A Col B
E:\My PICTURES\2004\20040326_1715.jpg hawai.jpg
E:\My PICTURES\2004\20040326_1698.jpg Kat25.jpg
E:\My PICTURES\2004\20040326_1700.jpg LLLL.jpg
E:\My PICTURES\2004\20040508_1697.jpg L05_0592.JPG

On sheet 2, I copied one of the many txt files of an application where
we can find lines
that refers to the old directory and filename of a picture.

lines...
cell[2].images[0].image=../../../E:\My PICTURES\Fam/Friends\kat25_.jpg
lines...
cell[32].images[0].image=../../../E:\My PICTURES\Fam/Friends\Kito6.jpg
lines...
cell[151].images[0].image=../../../E:\MyPICTURES\Others/cats\IMG_0256.JPG
lines...

I want to replace this old directory and filename
E:/My Pictures/Fam/Friends/kat25_.jpg
with the new directory and filename;
E:\My Pictures\2004\20040326_1698.jpg
but also keep the information that preceded the old filename &
directory.

Final result on sheet 2 should be;
cell[2].images[0].image=../../../E:\My Pictures\2004/20040326_1698.jpg

Any suggestions on a possible solution?

Den

First swap your two columns A & B on sheet 1 over so that the new file
name bcomes the subject value of a Vlookup. (You could probably use
offsets in the Formula below but I think that would unnecessarily
complicate it.

Then on Sheet 2, with your old folder/file names in A1:A..., put the
following in B1 and copy down.

=VLOOKUP((RIGHT(A1,MATCH("\",MID(A1,LEN(A1)-ROW(1:255),1),0))),Sheet1!A2:B5,2,FALSE)

This is an array formula, so enter with CTRL Shift and Enter

I assumed that there was a type above where you said that the second
file name in column B was Kat25.jpg, whereas your old line names
included Kat25_jpg - i.e. with an underscore character.

The '255' number is only there to catch all file names up to 255
characters in length.

HTH
Richard Buttrey
__

Sorry, missed off the front bit which concatenates the
cell[2].images...... bit

This assumes there is only one "\" before the reference to the Drive
"E:" reference. If not please post back.

=LEFT(A1,FIND("\",A1)-3)&VLOOKUP((RIGHT(A1,MATCH("\",MID(A1,LEN(A1)-ROW(1:255),1),0))),Sheet1!A2:B5,2,FALSE)

Rgds


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Hi Richard,


I hope that I understand your question;

The exact strings that comes *before* all the "E:" are

cell[2].images[0].image=../../../
or
cell[32].images[0].image=../../../
or
cell[151].images[0].image=../../../


So there will always be three (3) "/" before the "E:" if I count them
all from the start of the line.

A precision may be needed: on the sheet 2 where we find the txt file
of the application, there are only "/" and *no* "\"

But on the other hand on sheet 1 the new filename (in the computer)
comes with "\"

But this is not a problem since I can change all those "\" that may be
present to "/" after the new filename & directories have been writen
to the txt file.

Den
Canada

On Sun, 12 Mar 2006 17:16:04 -0500, Den wrote:

On Sun, 12 Mar 2006 18:11:55 +0000, Richard Buttrey
wrote:

On Sun, 12 Mar 2006 18:01:10 +0000, Richard Buttrey
wrote:

This assumes there is only one "\" before the reference to the Drive
"E:" reference. If not please post back.

=LEFT(A1,FIND("\",A1)-3)&VLOOKUP((RIGHT(A1,MATCH("\",MID(A1,LEN(A1)-ROW(1:255),1),0))),Sheet1!A2:B5,2,FALSE)

Rgds


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Hi Richard,


I hope that I understand your question;

The exact strings that comes *before* all the "E:" are

cell[2].images[0].image=../../../
or
cell[32].images[0].image=../../../
or
cell[151].images[0].image=../../../


So there will always be three (3) "/" before the "E:" if I count them
all from the start of the line.

A precision may be needed: on the sheet 2 where we find the txt file
of the application, there are only "/" and *no* "\"

But on the other hand on sheet 1 the new filename (in the computer)
comes with "\"

But this is not a problem since I can change all those "\" that may be
present to "/" after the new filename & directories have been writen
to the txt file.

Den
Canada

Hi Den,

Hope I understand your data. Thinking a little more about this, if the
constant is the "E:" bit in the string, then it would be smarter to
use that. Hence:

=LEFT(A1,FIND("E:",A1)-1)&VLOOKUP((RIGHT(A1,MATCH("\",MID(A1,LEN(A1)-ROW(1:255),1),0))),Sheet1!A2:B5,2,FALSE)

is a more general solution and avoids having to worry about whether
there is a "\" or not.

Hope this gives you what you want.

Rgds






__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

On Sun, 12 Mar 2006 23:24:35 +0000, Richard Buttrey
wrote:

On Sun, 12 Mar 2006 17:16:04 -0500, Den wrote:

On Sun, 12 Mar 2006 18:11:55 +0000, Richard Buttrey
wrote:

On Sun, 12 Mar 2006 18:01:10 +0000, Richard Buttrey
wrote:

This assumes there is only one "\" before the reference to the Drive
"E:" reference. If not please post back.

=LEFT(A1,FIND("\",A1)-3)&VLOOKUP((RIGHT(A1,MATCH("\",MID(A1,LEN(A1)-ROW(1:255),1),0))),Sheet1!A2:B5,2,FALSE)

Rgds


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Hi Richard,


I hope that I understand your question;

The exact strings that comes *before* all the "E:" are

cell[2].images[0].image=../../../
or
cell[32].images[0].image=../../../
or
cell[151].images[0].image=../../../


So there will always be three (3) "/" before the "E:" if I count them
all from the start of the line.

A precision may be needed: on the sheet 2 where we find the txt file
of the application, there are only "/" and *no* "\"

But on the other hand on sheet 1 the new filename (in the computer)
comes with "\"

But this is not a problem since I can change all those "\" that may be
present to "/" after the new filename & directories have been writen
to the txt file.

Den
Canada

Hi Den,

Hope I understand your data. Thinking a little more about this, if the
constant is the "E:" bit in the string, then it would be smarter to
use that. Hence:

=LEFT(A1,FIND("E:",A1)-1)&VLOOKUP((RIGHT(A1,MATCH("\",MID(A1,LEN(A1)-ROW(1:255),1),0))),Sheet1!A2:B5,2,FALSE)

is a more general solution and avoids having to worry about whether
there is a "\" or not.

Sorry, typo. I meant whether there is a "/" or not.

Rgds


Hope this gives you what you want.

Rgds






__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

On Sun, 12 Mar 2006 23:24:35 +0000, Richard Buttrey
wrote:

On Sun, 12 Mar 2006 17:16:04 -0500, Den wrote:

On Sun, 12 Mar 2006 18:11:55 +0000, Richard Buttrey
wrote:

On Sun, 12 Mar 2006 18:01:10 +0000, Richard Buttrey
wrote:

This assumes there is only one "\" before the reference to the Drive
"E:" reference. If not please post back.

=LEFT(A1,FIND("\",A1)-3)&VLOOKUP((RIGHT(A1,MATCH("\",MID(A1,LEN(A1)-ROW(1:255),1),0))),Sheet1!A2:B5,2,FALSE)

Rgds


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Hi Richard,


I hope that I understand your question;

The exact strings that comes *before* all the "E:" are

cell[2].images[0].image=../../../
or
cell[32].images[0].image=../../../
or
cell[151].images[0].image=../../../


So there will always be three (3) "/" before the "E:" if I count them
all from the start of the line.

A precision may be needed: on the sheet 2 where we find the txt file
of the application, there are only "/" and *no* "\"

But on the other hand on sheet 1 the new filename (in the computer)
comes with "\"

But this is not a problem since I can change all those "\" that may be
present to "/" after the new filename & directories have been writen
to the txt file.

Den
Canada

Hi Den,

Hope I understand your data. Thinking a little more about this, if the
constant is the "E:" bit in the string, then it would be smarter to
use that. Hence:

=LEFT(A1,FIND("E:",A1)-1)&VLOOKUP((RIGHT(A1,MATCH("\",MID(A1,LEN(A1)-ROW(1:255),1),0))),Sheet1!A2:B5,2,FALSE)

is a more general solution and avoids having to worry about whether
there is a "\" or not.

Hope this gives you what you want.

Rgds






__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________


Thank Richard for your help. I really appreciate.

Right now it doesn't work but you've given me hope that it can be
done.

I've tried to copy the formula into excel but I have no results with
CTRL+SHIFT+ENTER ; never use this combination of keys.

The formula just "sits" there in the cell and nothing appends;

I suspect that maybe I wasn't clear in my description of my data and
that some minor corrections may be needed.

I will have to struggle since this is beyond what I ever did before
with Excel.

Den