Formula to change part of the information in each column according to the respective row?

[michikorc]

Hi Excel Experts,

I am a newbie to this forum (and much of Excel's functions). I am wondering if it is in any way possible for me to come up with a formula for the following problem:

I have 3 columns, for e.g.:

SubjectID Full path to Pic1 Full path to Pic2
0001 /Users/Desktop/0001/animals/pig.jpg /Users/Desktop/0001/cow.jpg
0002
.
.
.

I would like to come up with a formula that automatically changes part of the path address, specifically to reflect the subject ID. For example, instead of manually copying/typing out /Users/Desktop/0002/animals/pig.jpg and /Users/Desktop/0002/animals/cow.jpg for Subject 0002, the formula would change '0001' to '0002' within the path address and this can be done for every column according to the ID in the respective row.

I am not sure if this can be done in Excel.. but it would be nearly impossible for me to manually do this as I have over 1000 folders/subjects!

This file will be saved as a tab delimited text file to be entered into a script subsequently.

Thanks very much in advance!

 

[Peter_SSs]

Welcome to the MrExcel board!

Is this what you mean? Formula in B3 is copied across and down.
If not please explain again with reference to specific before and after samples.

Book1
	A	B	C
2	0001	/Users/Desktop/0001/animals/pig.jpg	/Users/Desktop/0001/cow.jpg
3	0002	/Users/Desktop/0002/animals/pig.jpg	/Users/Desktop/0002/cow.jpg
4	0003	/Users/Desktop/0003/animals/pig.jpg	/Users/Desktop/0003/cow.jpg
Sheet2 (2)

Cell Formulas
Range	Formula
B3	=SUBSTITUTE(B2,"/"&$A2&"/","/"&$A3&"/")

 

[michikorc]

Welcome to the MrExcel board!

Is this what you mean? Formula in B3 is copied across and down.
If not please explain again with reference to specific before and after samples.

	A	B	C
/Users/Desktop/0001/animals/pig.jpg	/Users/Desktop/0001/cow.jpg
/Users/Desktop/0002/animals/pig.jpg	/Users/Desktop/0002/cow.jpg
/Users/Desktop/0003/animals/pig.jpg	/Users/Desktop/0003/cow.jpg

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]
[TD="align: right"]0001[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]0002[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]0003[/TD]

</tbody>

Sheet2 (2)​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B3[/TH]
[TD="align: left"]=SUBSTITUTE(B2,"/"&$A2&"/","/"&$A3&"/")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Hi Peter, thanks for the quick response. I think that's right, but is there any way for it to be an almost-automated process where I could simply copy and paste the formula across the cells instead of changing the formula in each cell to match the output required?
i.e. For the formula you kindly provided, it would still be necessary to go in and change for example, B2,A2,A3 variables.

Thank you!

 

[Peter_SSs]

Perhaps I have not understood your requirement.

i.e. For the formula you kindly provided, it would still be necessary to go in and change for example, B2,A2,A3 variables.

What would you be changing them to?

Could you give a small set of sample data (say 5-6 rows) showing both what it looks like 'before' anything is done and what it would look like 'after' whatever has to be done?