formatting a cell with text from other cells

[clrwtrms]

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

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

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

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

Thank yo

 

[tony h]

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

 

[clrwtrms]

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

 

[Guest]

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.

 

[Gord Dibben]

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