Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
R D S
 
Posts: n/a
Default 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


  #2   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

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



  #3   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

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



  #4   Report Post  
JMay
 
Posts: n/a
Default

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




  #5   Report Post  
2rrs
 
Posts: n/a
Default

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Adding Leading Zeros to Text Jenn Excel Discussion (Misc queries) 4 January 12th 05 07:51 PM
Adding cells, using text as number Pgsmom Excel Discussion (Misc queries) 4 January 10th 05 08:21 PM
Convert data of cells to any type: Number, Date&Time, Text Kevin Excel Discussion (Misc queries) 0 December 30th 04 07:55 AM
Adding + in front of a number Ted Metro Excel Discussion (Misc queries) 3 December 29th 04 11:45 PM
Converting text to number - how?? yahoo Excel Discussion (Misc queries) 3 December 16th 04 12:01 AM


All times are GMT +1. The time now is 08:26 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"