adding to a number containing text

R

R D S

Hi,
=(sum(A3+1) normally works great for me but now I am dealing with a number
which contains text ie 'PE10000', how do I write a formula to add 1 to this
to give 'PE10001'?

Many thanks.
Rick
 
P

Peo Sjoblom

If the text is always 3 letters to the left of the number

=IF(ISERR(--(A3)),LEFT(A3,2)&MID(A3,3,255)+1,A3+1)

if the text is always to the left of the number but can differ when it comes
to numbers of characters


=IF(ISERR(--(A3)),LEFT(A3,MIN(IF(ISNUMBER(FIND({1,2,3,4,5,6,7,8,9,0},A3)),FIND({1,2,3,4,5,6,7,8,9,0},A3)))-1)&MID(A3,MIN(IF(ISNUMBER(FIND({1,2,3,4,5,6,7,8,9,0},A3)),FIND({1,2,3,4,5,6,7,8,9,0},A3))),255)+1,A3+1)

entered with ctrl + shift & enter


Regards,

Peo Sjoblom
 
P

Peo Sjoblom

Correction

"If the text is always 3 letters to the left of the number"

should have been

If the text is always 2 letters to the left of the number
 
J

JMay

Select Cells to be effected:
At the Menu, select Format, Custom, in the box type in:
"PE1"000# and OK out.will work up to PE19999.
HTH
 
Top