excel formulas with text
 

I need a formula that will add 1 for each record of a reference number.
exaplme: my first reference number is jn51000001. I need a formula to copy
and paste down a good 20,000 cells. I can't use =sum(a1+1) because of the
text in it.
I desparately need help on a formula that will work.

Format the cell with a custom format, such as jn51000000
Then when you type a 1 in the cell it should be jn51000001, a 2 should
appear as jn51000002. And your formula need only be =A1+1

*******************
~Anne Troy

www.OfficeArticles.com


"MellowMe" wrote in message
...
I need a formula that will add 1 for each record of a reference number.
exaplme: my first reference number is jn51000001. I need a formula to
copy
and paste down a good 20,000 cells. I can't use =sum(a1+1) because of the
text in it.
I desparately need help on a formula that will work.

One way would be to go ahead and put 51000001 in A1 and 51000002 in A2 and
select both and drag down to A20000, then in B1 put ="jn"&A1 and copy down
and then Copy PasteSpecial Values on column B to eliminate the
formulas..........

Vaya con Dios,
Chuck, CABGx3



"MellowMe" wrote in message
...
I need a formula that will add 1 for each record of a reference number.
exaplme: my first reference number is jn51000001. I need a formula to
copy
and paste down a good 20,000 cells. I can't use =sum(a1+1) because of the
text in it.
I desparately need help on a formula that will work.

MellowMe wrote:
I need a formula that will add 1 for each record of a reference number.
exaplme: my first reference number is jn51000001. I need a formula to copy
and paste down a good 20,000 cells. I can't use =sum(a1+1) because of the
text in it.
I desparately need help on a formula that will work.

This is my first piece of data: test0000001 (it is located in cell
AF3)

This is formula to do what you want, to the first piece of data:

=CONCATENATE(MID(AF3,1,4),TEXT((MID(AF3,5,7)+1),"0 000000"))

Copy it down the column after you adjust for positions of your data in
initial string of jn51000001.

Use the excel help to figure out how MID and TEXT and CONCATENATE
functions work.

Hi,

Formula in A2 and copied down,

="jn"&--SUBSTITUTE(A1,"jn","")+1

where A1 houses jn51000001

HTH


--
Krishnakumar


------------------------------------------------------------------------
Krishnakumar's Profile: http://www.excelforum.com/member.php...o&userid=20138
View this thread: http://www.excelforum.com/showthread...hreadid=387666

Now, THAT is slick.............well done Krishnakumar!

Vaya con Dios,
Chuck, CABGx3



"Krishnakumar"
wrote in message
news:Krishnakumar.1s90md_1121493922.2683@excelforu m-nospam.com...

Hi,

Formula in A2 and copied down,

="jn"&--SUBSTITUTE(A1,"jn","")+1

where A1 houses jn51000001

HTH


--
Krishnakumar


------------------------------------------------------------------------
Krishnakumar's Profile:
http://www.excelforum.com/member.php...o&userid=20138
View this thread: http://www.excelforum.com/showthread...hreadid=387666