letters and numbers

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

is it possible to have text and a number in a cell. I
have a formula tha computes the number but i need to
designate the number with a letter code only some times
and the letter code changes.

I want to have the number in a cell and a letter
designation and i copy it to a different sheet. as it is
now if i put a letter into the cell it screws up my
formula.
 
is it possible to have text and a number in a cell. I
have a formula tha computes the number but i need to
designate the number with a letter code only some times
and the letter code changes.

I want to have the number in a cell and a letter
designation and i copy it to a different sheet. as it is
now if i put a letter into the cell it screws up my
formula.
Click to expand...

You don't give enough information for a solution to your problem. But the
answer to your question is YES you can have a number and text in a cell.

Whether your formula will be "screwed up" depends on what you are doing with
the formula.

How to combine the text and number depends on the rules for combinations.


--ron
 
c7 and the rest of the cells on row 7 i may need to put a
letter in the cell but not all the time. i would like to
put a number and a letter example 12 V or 12 S
the letter just to designates the number it is not needed
for the formula.
as it is now the formula below will not calculate c7 when
i place a letter in it with a number and i don't know how
to fix this can you help.
=MIN(SUM(C5:C7),40+SUM($C$7:$J$7))
 
c7 and the rest of the cells on row 7 i may need to put a
letter in the cell but not all the time. i would like to
put a number and a letter example 12 V or 12 S
the letter just to designates the number it is not needed
for the formula.
as it is now the formula below will not calculate c7 when
i place a letter in it with a number and i don't know how
to fix this can you help.
=MIN(SUM(C5:C7),40+SUM($C$7:$J$7))
Click to expand...

Assuming the letter is random; always is just a single letter; and is at the
end, then the following array formula should work:

=MIN(SUM(IF(ISTEXT(C5:C7),--LEFT(C5:C7,LEN(C5:C7)-1),C5:C7)),
40+SUM(IF(ISTEXT($C$7:$J$7),
--LEFT($C$7:$J$7,LEN($C$7:$J$7)-1),$C$7:$J$7)))

To enter an array formula, hold down <ctrl><shift> while hitting <enter>. XL
will place braces {...} around the formula.


--ron
 
Assuming the letter is random; always is just a single letter; and is at
the
end, then the following array formula should work:

=MIN(SUM(IF(ISTEXT(C5:C7),--LEFT(C5:C7,LEN(C5:C7)-1),C5:C7)),
40+SUM(IF(ISTEXT($C$7:$J$7),
--LEFT($C$7:$J$7,LEN($C$7:$J$7)-1),$C$7:$J$7)))

To enter an array formula, hold down <ctrl><shift> while hitting
<enter>. XL
will place braces {...} around the formula.


--ron
Click to expand...

Another trick I have used is to use the Format, Cell, Custom Number method
e.g. if I have a number that represents distance in kilometers, I format
it as 0.00 "km" in the Format, Cell dialog (under the Custom setting) and
copy the format to each cell as required. This way, the letter designator
is not actually stored as part of the value in the cell and I can do
whatever calculations I like.

This works best where an entire range of cells (row, column or
combination) is formatted the same way - it's a bit tedious when it needs
to be done on one cell at a time within a range.
 
This works best where an entire range of cells (row, column or
combination) is formatted the same way - it's a bit tedious when it needs
to be done on one cell at a time within a range.
Click to expand...

That's why I asked the OP if there were some rule concerning the association of
letters with numbers. If there were, it might be able to be applied by either
a custom formatting option or a VBA event-driven macro.

He did not respond to that question, so my formula assumes the letters are
assigned randomly.


--ron
 
i tried this below and could not get it to work. im not a
master at excel, but i thought i was pretty good. i have
no clue i guess.

=MIN(SUM(IF(ISTEXT(C5:C7),--LEFT(C5:C7,LEN(C5:C7)-
1),C5:C7)),
40+SUM(IF(ISTEXT($C$7:$J$7),
--LEFT($C$7:$J$7,LEN($C$7:$J$7)-1),$C$7:$J$7)))
 
i tried this below and could not get it to work. im not a
master at excel, but i thought i was pretty good. i have
no clue i guess.

=MIN(SUM(IF(ISTEXT(C5:C7),--LEFT(C5:C7,LEN(C5:C7)-
1),C5:C7)),
40+SUM(IF(ISTEXT($C$7:$J$7),
--LEFT($C$7:$J$7,LEN($C$7:$J$7)-1),$C$7:$J$7)))
Click to expand...

What does "could not get it to work" mean? What exactly happened?

It works fine on my machine given the inputs you stated. Are you using inputs
other than what you indicated in your posting?

Did you properly enter it as an array formula, as I had stated in my message?


--ron
 
That's why I asked the OP if there were some rule concerning the
association of
letters with numbers. If there were, it might be able to be applied by
either
a custom formatting option or a VBA event-driven macro.

He did not respond to that question, so my formula assumes the letters
are
assigned randomly.
Click to expand...

Agreed. I wasn't suggesting my way was better, merely an alternative
method that has worked for me in the situation I specified.
 
Back
Top