adding to a number containing text
 

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

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



"R D S" wrote:

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

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



"Peo Sjoblom" wrote:

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



"R D S" wrote:

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

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

"R D S" wrote in message
...
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

One way:
Assumes PE10000 in A3

In A4 enter: =IF(A3<"","PE"&ROW()+9997,"")
Fill Down


R D S wrote:
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