Number and Word in the same cell

[Guest]

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

 

[Guest]

If

A1 = 6 meters
A2 = 11.9 meters

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

 

[Guest]

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

General" meters" The value can be used directly.

 

[Guest]

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

Thanks!

 

[nowfal]

Hi BizMark,
I got a long troubling solution by your answer. Thank yo
very much. Now a related question, if you kindly solve this, it helps
lot to me.
One of a cell having Amount which i convert now as 5000/= (like yo
suggest number "/="). But when small denomination comes, i wanted th
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 othe
alternative solution.
Thanks and regards.
NOWFA

 

[nowfal]

hi,
Anybody can help

 

[Ragdyer]

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

 

[nowfal]

Hi Ragdyer,
Thanks for the reply. What you mention is below
1000. My requirement is any amount which is having cents should show
like 1000.50 and which is not having cents should be like 1000/=
I hope you got my problem.
thanks and regards
NOWFAL

 

[RagDyer]

I don't think it can be done.

BUT ... others may have some ideas.

 

[nowfal]

Hi Ragdyer,
Thanks for reply. I also thinking that way. Hope
somebody will have some new tricks.
nowfal

 

[Bryan Hessey]

Perhaps

=IF(MOD(A1,1)<>0,TEXT(A1,"0000.00")&" metres",TEXT(A1,"0000")&"/
metres")

will suit your needs, but the question does seem to have drifted
little from metres to cents.

 

[nowfal]

Yes, exactly right formula. This is why this forum seems to be the best
thanks to
Bryan Hessey, and all those help early.
the corrected formula is as
=IF(MOD(a1,1)<>0,TEXT(a1,"0.00")&" ",TEXT(a1,"0")&"/= ")
thanks and regards
nowfa