I want to be able to have a number and a word in the same cell, but still be
able to use formulas to add, subtract, ect. to the number.
For example: I have 6 meters in cell A1 and want to add 11.9 meters

If

A1 = 6 meters
A2 = 11.9 meters

=VALUE(LEFT(A1,SEARCH(" ",A1,1)-1))+VALUE(LEFT(A2,SEARCH(" ",A2,1)-1))

"Bob Martin" wrote:

I want to be able to have a number and a word in the same cell, but still be
able to use formulas to add, subtract, ect. to the number.
For example: I have 6 meters in cell A1 and want to add 11.9 meters

There's an easier way than that. If you simply type into

A1 = 6
A2 = 11.9

as pure numbers (i.e. omit the word "metres"), you can then select A1 and A2, go to the 'Format' menu 'Cells...' and on the 'Number' tab, choose 'Custom' type number formatting.

Then, for the format string (which will by default in this instance read 0 or 0.0 or General), type in 0.0 "metres" - i.e. zero point zero, followed by a space, followed by quote, followed by the word metres, followed by another quote.

Your cells will then appear like this:
A1 = 6.0 metres
A2 = 11.9 metres

BUT the underlying cell contents are purely the numeric values 6 and 11.9, and can therefore be calculated upon without having to trim the strings out. e.g. you could total them simply with =A1+A2. Simply apply the same formatting as described above to get the result to also append the phrase "metres".

BizMark

An alternative method is to format Cell Number Custom to something like

General" meters" The value can be used directly.
--
Gary''s Student


"JMB" wrote:

If

A1 = 6 meters
A2 = 11.9 meters

=VALUE(LEFT(A1,SEARCH(" ",A1,1)-1))+VALUE(LEFT(A2,SEARCH(" ",A2,1)-1))

"Bob Martin" wrote:

I want to be able to have a number and a word in the same cell, but still be
able to use formulas to add, subtract, ect. to the number.
For example: I have 6 meters in cell A1 and want to add 11.9 meters

Never seen or tried that trick before. That's pretty cool.

Thanks!

"JMB" wrote:

If

A1 = 6 meters
A2 = 11.9 meters

=VALUE(LEFT(A1,SEARCH(" ",A1,1)-1))+VALUE(LEFT(A2,SEARCH(" ",A2,1)-1))

"Bob Martin" wrote:

I want to be able to have a number and a word in the same cell, but still be
able to use formulas to add, subtract, ect. to the number.
For example: I have 6 meters in cell A1 and want to add 11.9 meters

Hi BizMark,
I got a long troubling solution by your answer. Thank you
very much. Now a related question, if you kindly solve this, it helps a
lot to me.
One of a cell having Amount which i convert now as 5000/= (like you
suggest number "/="). But when small denomination comes, i wanted the
same cell to be like normal earlier format that is like this 5000.50
Is it possible by using "IF" formula or do you have any other
alternative solution.
Thanks and regards.
NOWFAL


--
nowfal
------------------------------------------------------------------------
nowfal's Profile: http://www.excelforum.com/member.php...o&userid=10003
View this thread: http://www.excelforum.com/showthread...hreadid=477776

hi,
Anybody can help?


--
nowfal
------------------------------------------------------------------------
nowfal's Profile: http://www.excelforum.com/member.php...o&userid=10003
View this thread: http://www.excelforum.com/showthread...hreadid=477776

Don't know if I exactly understand what you're asking.

Use this custom format to display values = 1000 as:
1000/=
And values under 1000 as:
999.00

[<1000]0.00;0"/="
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"nowfal" wrote in
message ...

hi,
Anybody can help?


--
nowfal
------------------------------------------------------------------------
nowfal's Profile:
http://www.excelforum.com/member.php...o&userid=10003
View this thread: http://www.excelforum.com/showthread...hreadid=477776