| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Number and Word in the same cell
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 |
|
#2
|
|||
|
|||
|
Number and Word in the same cell
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 |
|
#3
|
|||
|
|||
|
Number and Word in the same cell
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 |
|
#4
|
|||
|
|||
|
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 Last edited by BizMark : October 20th 05 at 10:39 AM |
|
#5
|
|||
|
|||
|
Number and Word in the same cell
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 |
|
#6
|
|||
|
|||
|
Number and Word in the same cell
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 |
|
#7
|
|||
|
|||
|
Number and Word in the same cell
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 |
|
#8
|
|||
|
|||
|
Number and Word in the same cell
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 |
|
#9
|
|||
|
|||
|
Number and Word in the same cell
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 -- nowfal ------------------------------------------------------------------------ nowfal's Profile: http://www.excelforum.com/member.php...o&userid=10003 View this thread: http://www.excelforum.com/showthread...hreadid=477776 |
|
#10
|
|||
|
|||
|
Number and Word in the same cell
I don't think it can be done.
BUT ... others may have some ideas. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "nowfal" wrote in message ... 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 -- nowfal ------------------------------------------------------------------------ nowfal's Profile: http://www.excelforum.com/member.php...o&userid=10003 View this thread: http://www.excelforum.com/showthread...hreadid=477776 |
|
#11
|
|||
|
|||
|
Number and Word in the same cell
Hi Ragdyer, Thanks for reply. I also thinking that way. Hope somebody will have some new tricks. nowfal -- nowfal ------------------------------------------------------------------------ nowfal's Profile: http://www.excelforum.com/member.php...o&userid=10003 View this thread: http://www.excelforum.com/showthread...hreadid=477776 |
|
#12
|
|||
|
|||
|
Number and Word in the same cell
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 a little from metres to cents. nowfal Wrote: Hi Ragdyer, Thanks for reply. I also thinking that way. Hope somebody will have some new tricks. nowfal -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=477776 |
|
#13
|
|||
|
|||
|
Number and Word in the same cell
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 nowfal -- nowfal ------------------------------------------------------------------------ nowfal's Profile: http://www.excelforum.com/member.php...o&userid=10003 View this thread: http://www.excelforum.com/showthread...hreadid=477776 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Number in cell convert in word | Excel Worksheet Functions | |||
| Max number if characters in a merged cell | Excel Discussion (Misc queries) | |||
| How do I link a certain number to a certain word? | Excel Worksheet Functions | |||
| copy a number typed in a cell to another & change it to word form. | Excel Discussion (Misc queries) | |||
| Counting the number of times a word appears in a worksheet | Excel Worksheet Functions | |||
Linear Mode
