Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding Leading Zeros to Text | Excel Discussion (Misc queries) | |||
Adding cells, using text as number | Excel Discussion (Misc queries) | |||
Convert data of cells to any type: Number, Date&Time, Text | Excel Discussion (Misc queries) | |||
Adding + in front of a number | Excel Discussion (Misc queries) | |||
Converting text to number - how?? | Excel Discussion (Misc queries) |