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

Hi!

You don't/can't add to it because it is only a word - a piece of text. But you can change its last character from 0 to 1.

If PE1000 is in A1 put the following in B1:

=LEFT(A1,LEN(A1)-1)&"1".

Begs the question of what happens if it is PE10006 or (more complicated) PE10009 to which you wish to "add" 1.

With PE10006, just put "7" instead of "1".
In the case of "9" becoming "10" then the formula would be

=LEFT(A1,LEN(A1)-2)&"10"

Must give some thought to generalising it: I guess there are 2 routes just asking to be explored.

Alf

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
> >
> >
> >

Hi!

Second bite:

=LEFT(A1,2)&TEXT(VALUE(RIGHT(A1,LEN(A1)-2))+1,"#####")

seems to do the job if you put it in B1.

It looks horrendous, but it's simple: separate the "PE" and the "number": convert the "number" to a real number: add 1: convert to text again and then join it up to the PE bit again.

Alf

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" <[email protected]> wrote in message
news:[email protected]...
> 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