Changing part of a file name - Part B

[Aidworker17]

Dear All,

The layout of the changes I am looking for have not displayed neatly - so I have added the excel file to show what I would like.

Is there a formula that can change a large column of file names like below ?

I know about TEXT(<cell>,"00000"), but I have groups of file names where some have the words “Not Captured” and some don’t …

From To
FilenameA (1) FilenameA00001
FilenameA (2) Not Captured FilenameA00002 Not Captured
FilenameA (3) FilenameA00003
FilenameA (4) FilenameA00004
FilenameA (5) Not Captured FilenameA00005 Not Captured
FilenameA (6) Not Captured FilenameA00006 Not Captured
FilenameA (7) FilenameA00007
FilenameA (8) Not Captured FilenameA00008 Not Captured
FilenameA (9) Not Captured FilenameA00009 Not Captured
FilenameA (10) FilenameA00010
...
FilenameA (100) FilenameA00100
FilenameA (101) Not Captured FilenameA00101 Not Captured
etc ...

Also, the column continues but the group of file names change - so I would also like the formula to make the changes below.

However, FilenameBbbb and FilenameCcccccc may be shorter or longer than FilenameA. (I am happy to use 1 formula and manually apply it to each group of file names) :

From To
FilenameBbbb (1) FilenameBbbb00001
FilenameBbbb (2) Not Captured FilenameBbbb00002 Not Captured
FilenameBbbb (3) FilenameBbbb00003
FilenameBbbb (4) FilenameBbbb00004
FilenameBbbb (5) Not Captured FilenameBbbb00005 Not Captured
FilenameBbbb (6) Not Captured FilenameBbbb00006 Not Captured
FilenameBbbb (7) FilenameBbbb00007
FilenameBbbb (8) Not Captured FilenameBbbb00008 Not Captured
FilenameBbbb (9) Not Captured FilenameBbbb00009 Not Captured
FilenameBbbb (10) FilenameBbbb00010
...
FilenameBbbb (100) FilenameBbbb00100
FilenameBbbb (101) Not Captured FilenameBbbb00101 Not Captured
etc ...

From To
FilenameCcccccc (1) FilenameCcccccc00001
FilenameCcccccc (2) Not Captured FilenameCcccccc00002 Not Captured
FilenameCcccccc (3) FilenameCcccccc00003
FilenameCcccccc (4) FilenameCcccccc00004
FilenameCcccccc (5) Not Captured FilenameCcccccc00005 Not Captured
FilenameCcccccc (6) Not Captured FilenameCcccccc00006 Not Captured
FilenameCcccccc (7) FilenameCcccccc00007
FilenameCcccccc (8) Not Captured FilenameCcccccc00008 Not Captured
FilenameCcccccc (9) Not Captured FilenameCcccccc00009 Not Captured
FilenameCcccccc (10) FilenameCcccccc00010
...
FilenameCcccccc (100) FilenameCcccccc00100
FilenameCcccccc (101) Not Captured FilenameCcccccc00101 Not Captured
etc ...

Kind regards,
Jon

 

[bosco_yip]

Try,

In C3, copied down :

=IF(A3="","",TRIM(LEFT(A3,FIND("(",A3)-2)&TEXT(MID(A3,FIND("(",A3)+1,FIND(")",A3)-FIND("(",A3)-1),"00000")&MID(A3,FIND(")",A3)+1,99)))

Regards
Bosco

 

[Aidworker17]

Dear Bosco,

Simply brilliant.

Where I come from, the clinical efficiency of your solution is referred to as "Champagne Football".

Kind Regards,
Jon