okay I have a VERY SIMPLE spreadsheet that is used to create shipping
labels

I need to be able to have a cell formatted so that it reads like
this...

p (in this space have the value that is alpha numeric from a cell above
it) -01

and there can be no spaces as it's a barcode so basically if the Part
number is 1234 the barcode below it needs to read p1234-01 however have
the spot above it read PN: 1234, the way I have it setup right now is
that PN: is in one cell and the 1234 in another, Please help. I am
sure this has been asked umptien times but I am a new excelforum user
and didn't even know how to go about searching for this.

Thank you


--
clrwtrms
------------------------------------------------------------------------
clrwtrms's Profile: http://www.excelforum.com/member.php...o&userid=29703
View this thread: http://www.excelforum.com/showthread...hreadid=494154

="p"&C8&"-1" where c8 is the cell reference


--
tony h
------------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
View this thread: http://www.excelforum.com/showthread...hreadid=494154

tony h Wrote:
="p"&C8&"-1" where c8 is the cell reference

thank you, I typed that in and all it displayed was exactly what you
typed. so it actually displayed ="p"&b1&"-1" and not p(variable)-1
like I had hoped


I am an excel noob, do I have to format the cell a certain way. Thanks
for your help


--
clrwtrms
------------------------------------------------------------------------
clrwtrms's Profile: http://www.excelforum.com/member.php...o&userid=29703
View this thread: http://www.excelforum.com/showthread...hreadid=494154

I'm not sure if I follow everything you are saying, but, if the cell above
the cell that you are putting this formula has, for instance PN1234, and you
want the cell with the formula to read P1234-01, and, if the digits will
always be 4 digits long, this formula should work. Note: as soon as you hit
the equal sign (=) you will be in formula mode. Reading your reply to
another post, it doesn't appear that you were in formula entry mode. Perhaps
you entered a space first? = needs to be the first character you type in
that cell. So type the following:
="P" & right(
Now use your up arrow key once to point to the cell above it, and that cell
will be entered in your formula. For instance, if you are in Cell A2 when
you start the formula, when you hit the up arrow key, your formula will now
look like this:
="P" & right(A1
now continue typing the following and it will be included in your formula:
, 4) & "-01"
So now your complete formula looks like this:
="P" & right(A1, 4) & "-01"
Hit Enter and it should do what you want. However, if it won't always be 4
digits, but will always start with PN, you could change the formula as
follows:
="P" & mid(A1, 3, len(A1) -2) & "-01"
Let me just paste these back to Excel and make sure I didn't make any
mistakes ...
Looks ok to me. HTH.

"clrwtrms" wrote:


okay I have a VERY SIMPLE spreadsheet that is used to create shipping
labels

I need to be able to have a cell formatted so that it reads like
this...

p (in this space have the value that is alpha numeric from a cell above
it) -01

and there can be no spaces as it's a barcode so basically if the Part
number is 1234 the barcode below it needs to read p1234-01 however have
the spot above it read PN: 1234, the way I have it setup right now is
that PN: is in one cell and the 1234 in another, Please help. I am
sure this has been asked umptien times but I am a new excelforum user
and didn't even know how to go about searching for this.

Thank you


--
clrwtrms
------------------------------------------------------------------------
clrwtrms's Profile: http://www.excelforum.com/member.php...o&userid=29703
View this thread: http://www.excelforum.com/showthread...hreadid=494154

Do all formulas display in this manner?

Hit CTRL + `(back quote) to toggle "View Formulas" off.

If just this one, it was probably formatted as text.

Format to General then F2 and <ENTER


Gord Dibben Excel MVP

On Fri, 16 Dec 2005 09:55:34 -0600, clrwtrms
wrote:

thank you, I typed that in and all it displayed was exactly what you
typed. so it actually displayed ="p"&b1&"-1" and not p(variable)-1
like I had hoped


I am an excel noob, do I have to format the cell a certain way. Thanks
for your help