Cell contents?

  • Thread starter Thread starter CWLee
  • Start date Start date
C

CWLee

Is it possible for a cell to contain a numerical value,
which is used for various calculations, and an alphabetical
designator that prints but does not affect calculations?
Example:

If cell A1 contains an entry of 6, and cell A2 contains an
entry of 6z, is there a way for the addition of the two
cells to show 12, or their multiplication to show 36, yet
still display 6z in cell A2 when the sheet is printed?

Thanks.

--
 
You could use a custom format in A2

0"z"

and the displayed and printed value will be 6z but you can still

do

=A1+A2



--


Regards,


Peo Sjoblom
 
You can do this with a combination of functions for your simple example

=A1*VALUE(LEFT(A2,1))

It is doubtful your example is as simple as you show, but this may get you
started?

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.nickhodge.co.uk

UK Excel User Conference In Cambridge
November 28th - December 1st 2007
Register Now @ http://www.exceluserconference.com/UKEUC.html
 
Try this:

=A1*LEFT(A2,1)

to give 36, or

=A1+LEFT(A2,1)

to give 12.

If you will only have one letter, but the number could be larger than
9, then it's probably safer to use:

LEFT(A2,LEN(A2)-1)

to extract the numeric part.

Hope this helps.

Pete
 
Thanks. The other two responses involved the cell in which
the calculations to be made are described. Your reply
suggests a method to do this in the cell in which I want to
include the letter as well as a number. I prefer your
approach, but am having trouble understanding it.

I have read the Excel help section, and find no description
of how to customize a cell to do what I want. There are
several formats shown under customizing cells, but it is not
clear if any of them are what you mean when you write:

O"z" (or perhaps you wrote 0"z"?)

I'd appreciate it if you would elaborate on your method.

Many thanks.

====================
 
OK

select A2, click the format menu in the toolbar and select
cells>number>custom or press Ctrl and 1 on the keyboard and select custom.
Place the cursor in the Type box to the right and type

0"z"

over whatever is in there when you select it (it is probably General if the
cell has no prior formatting)

then click OK

now type a number like 6 in A2 and you will see 6z


--


Regards,


Peo Sjoblom
 
Thanks. I did that, and I do get 6z in cell A2. I put 6 in
cell A3, and then in cell A4 I entered =(A2)+(A3) and what
came up was 12z, not the 12 I wanted. Strangely, when in
cell A5 I entered
=(A2)*(A3) the result was 36 - which is what I wanted.

It seems addition brought the z to the sum, but
multiplication ignored the z. Can you explain that?

Also, is there a way to use a letter other than z?

Thanks again.

===========================
 
Excel (in trying to be helpful) often takes the format from the
cell(s) you are using in a formula, which is what will have happened
in your first example. If you click on A4 and Format | Cells | Number
tab, you will see that it has the same format as A2 - just set it to
General or Number to get rid of the z in the answer.

If you follow Peo's directions once more, you can put any letter (or
group of letters) in the format instead of "z".

Hope this helps.

Pete
 
Your solution worked perfectly. Many thanks to you and
others.

=====================

Pete_UK said:
Excel (in trying to be helpful) often takes the format from the
cell(s) you are using in a formula, which is what will have happened
in your first example. If you click on A4 and Format | Cells | Number
tab, you will see that it has the same format as A2 - just set it to
General or Number to get rid of the z in the answer.

If you follow Peo's directions once more, you can put any letter (or
group of letters) in the format instead of "z".

Hope this helps.

Pete
Click to expand...
 
You're welcome - glad to help.

Pete

Your solution worked perfectly. Many thanks to you and
others.

=====================




Excel (in trying to be helpful) often takes the format from the
cell(s) you are using in a formula, which is what will have happened
in your first example. If you click on A4 and Format | Cells | Number
tab, you will see that it has the same format as A2 - just set it to
General or Number to get rid of the z in the answer.
Click to expand...
Click to expand...
 
Back
Top