Problem with incrementing Numbers

[Marcin298]

#hello

Hi, i’m new here and just curious if it’s possible to solve my problem… so…

In column A i got 25 cells with same value:

myphotos_paris_142500.jpg

How to simply rename all that cells in columns with incrementing number 500? Cell A 1 should be:

myphotos_paris_142500.jpg

And next should be:

myphotos_paris_142501.jpg
myphotos_paris_142502.jpg

And max 142999.jpg.

Is it possible by search&swap tool or in cell A 2 and higher i should add a formula? Please help me ?

 

[Trebor76]

Hi Marcin298,

Welcome to MrExcel!!

Put this formula into cell A2...

=LEFT(A1,15)&VALUE(MID(A1,16,6))+1&".jpg"

...and then copy it down the range A3:A500

Regards,

Robert

 

[jtakw]

Hi,

Enter formula in A2 and copy down, anything over your "max", just delete:

Book3.xlsx
	A
1	myphotos_paris_142500.jpg
2	myphotos_paris_142501.jpg
3	myphotos_paris_142502.jpg
4	myphotos_paris_142503.jpg
5	myphotos_paris_142504.jpg
6	myphotos_paris_142505.jpg
7	myphotos_paris_142506.jpg
8	myphotos_paris_142507.jpg
9	myphotos_paris_142508.jpg
10	myphotos_paris_142509.jpg
11	myphotos_paris_142510.jpg
12	myphotos_paris_142511.jpg
Sheet977

Cell Formulas
Range	Formula
A2:A12	A2	=LEFT(A1,15)&MID(A1,16,6)+1&RIGHT(A1,4)

 

[Marcin298]

Hi,

Enter formula in A2 and copy down, anything over your "max", just delete:

Book3.xlsx
	A
1	myphotos_paris_142500.jpg
2	myphotos_paris_142501.jpg
3	myphotos_paris_142502.jpg
4	myphotos_paris_142503.jpg
5	myphotos_paris_142504.jpg
6	myphotos_paris_142505.jpg
7	myphotos_paris_142506.jpg
8	myphotos_paris_142507.jpg
9	myphotos_paris_142508.jpg
10	myphotos_paris_142509.jpg
11	myphotos_paris_142510.jpg
12	myphotos_paris_142511.jpg
Sheet977

Cell Formulas
Range	Formula
A2:A12	A2	=LEFT(A1,15)&MID(A1,16,6)+1&RIGHT(A1,4)

Ok. And how to do it with more harder names?

Start cell is F14 with cell value:

1111111111^^^^OTHERS^^_ENT_GDN_GTW_CO_20160401_142500.pdf

Next should be as previous 142501; 142502 and so on…

I really don’t get it that formula and want it to use for various cell values.

 

[Marcin298]

Hi,

Enter formula in A2 and copy down, anything over your "max", just delete:

Book3.xlsx
	A
1	myphotos_paris_142500.jpg
2	myphotos_paris_142501.jpg
3	myphotos_paris_142502.jpg
4	myphotos_paris_142503.jpg
5	myphotos_paris_142504.jpg
6	myphotos_paris_142505.jpg
7	myphotos_paris_142506.jpg
8	myphotos_paris_142507.jpg
9	myphotos_paris_142508.jpg
10	myphotos_paris_142509.jpg
11	myphotos_paris_142510.jpg
12	myphotos_paris_142511.jpg
Sheet977

Cell Formulas
Range	Formula
A2:A12	A2	=LEFT(A1,15)&MID(A1,16,6)+1&RIGHT(A1,4)

Sir; could you Please help me with it and explain a lil bit more? I want to understand it and use with various cells values. It’s rly important to me. I will be really appriciated.

 

[Marcin298]

Hi,

Enter formula in A2 and copy down, anything over your "max", just delete:

Book3.xlsx
	A
1	myphotos_paris_142500.jpg
2	myphotos_paris_142501.jpg
3	myphotos_paris_142502.jpg
4	myphotos_paris_142503.jpg
5	myphotos_paris_142504.jpg
6	myphotos_paris_142505.jpg
7	myphotos_paris_142506.jpg
8	myphotos_paris_142507.jpg
9	myphotos_paris_142508.jpg
10	myphotos_paris_142509.jpg
11	myphotos_paris_142510.jpg
12	myphotos_paris_142511.jpg
Sheet977

Cell Formulas
Range	Formula
A2:A12	A2	=LEFT(A1,15)&MID(A1,16,6)+1&RIGHT(A1,4)

And it does not work on excel 2k16 :/

 

[MARK858]

And it does not work on excel 2k16 :/

There is no reason the formula @jtakw posted (or the formula @Trebor76 posted, as it is basically the same formula) wouldn't work with 2016, all the functions existed in that version.

LEFT(A1,15) gives the first 15 characters i.e. myphotos_paris_
&
MID(A1,16,6)+1 gives the 6 numbers starting at the 16th character plus 1 added to the number i.e. 142501
&
RIGHT(A1,4) gives the last 4 characters i.e. .jpg

Your new formula would be as below

Book1
	A
1	1111111111^^^^OTHERS^^_ENT_GDN_GTW_CO_20160401_142500.pdf
2	1111111111^^^^OTHERS^^_ENT_GDN_GTW_CO_20160401_142501.pdf
3	1111111111^^^^OTHERS^^_ENT_GDN_GTW_CO_20160401_142502.pdf
4	1111111111^^^^OTHERS^^_ENT_GDN_GTW_CO_20160401_142503.pdf
5	1111111111^^^^OTHERS^^_ENT_GDN_GTW_CO_20160401_142504.pdf
6	1111111111^^^^OTHERS^^_ENT_GDN_GTW_CO_20160401_142505.pdf
7	1111111111^^^^OTHERS^^_ENT_GDN_GTW_CO_20160401_142506.pdf
8	1111111111^^^^OTHERS^^_ENT_GDN_GTW_CO_20160401_142507.pdf
9	1111111111^^^^OTHERS^^_ENT_GDN_GTW_CO_20160401_142508.pdf
10	1111111111^^^^OTHERS^^_ENT_GDN_GTW_CO_20160401_142509.pdf
Sheet1

Cell Formulas
Range	Formula
A2:A10	A2	=LEFT(A1,47)&MID(A1,48,6)+1&RIGHT(A1,4)

 

[Marcin298]

There is no reason the formula @jtakw posted wouldn't work with 2016, all the functions existed in that version.

LEFT(A1,15) gives the first 15 characters i.e. myphotos_paris_
&
MID(A1,16,6)+1 gives the 6 numbers starting at the 16th character plus 1 added to the number i.e. 142501
&
RIGHT(A1,4) gives the last 4 characters i.e. .jpg

Your new formula would be as below

Book1
	A
1	1111111111^^^^OTHERS^^_ENT_GDN_GTW_CO_20160401_142500.pdf
2	1111111111^^^^OTHERS^^_ENT_GDN_GTW_CO_20160401_142501.pdf
3	1111111111^^^^OTHERS^^_ENT_GDN_GTW_CO_20160401_142502.pdf
4	1111111111^^^^OTHERS^^_ENT_GDN_GTW_CO_20160401_142503.pdf
5	1111111111^^^^OTHERS^^_ENT_GDN_GTW_CO_20160401_142504.pdf
6	1111111111^^^^OTHERS^^_ENT_GDN_GTW_CO_20160401_142505.pdf
7	1111111111^^^^OTHERS^^_ENT_GDN_GTW_CO_20160401_142506.pdf
8	1111111111^^^^OTHERS^^_ENT_GDN_GTW_CO_20160401_142507.pdf
9	1111111111^^^^OTHERS^^_ENT_GDN_GTW_CO_20160401_142508.pdf
10	1111111111^^^^OTHERS^^_ENT_GDN_GTW_CO_20160401_142509.pdf
Sheet1

Cell Formulas
Range	Formula
A2:A10	A2	=LEFT(A1,47)&MID(A1,48,6)+1&RIGHT(A1,4)

Thank you for ur help but look at that. This means that formula is wrong :/

 

[MARK858]

Are you sure that the separators on your version are

,

and not

;

 

[Marcin298]

Are you sure that the separators on your version are

,

and not

;

Thank you so much! But in my Polish Excel version commands LEFT;MID;RIGHT; are not supported :/ what’s rly weird proper command in photo. I’m really glad for support.