Trouble doing a formula for excel

  • Thread starter Thread starter Jason
  • Start date Start date
J

Jason

Hi All

I have a spreadsheet with the following
A1: z:\data/pc32/tsheets\unsorder00039.csv

I would like to add 1 too the number to make unsorder00040.csv and so
I have try mid,right,left i can't seem to do it

Cheers
 
Jason said:
Hi All

I have a spreadsheet with the following
A1: z:\data/pc32/tsheets\unsorder00039.csv

I would like to add 1 too the number to make unsorder00040.csv and so
I have try mid,right,left i can't seem to do it

Cheers
Click to expand...

A2: =MID(A1,LEN(A1)-8,5) = 00039
A3: =TEXT(A2+1,"00000") =00040
A4: =LEFT(A1,LEN(A1)-9) =z:\data/pc32/tsheets\unsorder
A5 =A4&A3&RIGHT(A1,4) = z:\data/pc32/tsheets\unsorder00040.csv

or

A2: =LEFT(A1,LEN(A1)-9)&TEXT(MID(A1,LEN(A1)-8,5)+1,"00000")&RIGHT(A1,4)


Andy
 
Try this formula...

=LEFT(A1,LEN(A1)-9)&TEXT(LEFT(RIGHT(A1,9),5)+1,"00000.")&RIGHT(A1,3)
 
Or shortening the formula by one character...

=LEFT(A1,LEN(A1)-9)&TEXT(LEFT(RIGHT(A1,9),5)+1,"00000")&RIGHT(A1,4)
 
Shortening by another 7 characters and one function call...

=LEFT(A1,LEN(A1)-9)&TEXT(LEFT(RIGHT(A1,9),5)+1,"00000.c\sv")
 
Back
Top