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
0
BobMartin (3)
10/20/2005 3:21:01 AM
excel.misc 78881 articles. 5 followers. Follow

12 Replies
652 Views

Similar Articles

[PageSpeed] 46

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
0
jmb (270)
10/20/2005 4:05:03 AM
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
0
GarysStudent (1572)
10/20/2005 6:47:03 AM
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


-- 
BizMark
0
10/20/2005 9:36:53 AM
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
0
jmb (270)
10/20/2005 11:03:01 PM
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

--
nowfa
-----------------------------------------------------------------------
nowfal's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1000
View this thread: http://www.excelforum.com/showthread.php?threadid=47777

0
10/22/2005 10:46:56 AM
hi,
Anybody can help

--
nowfa
-----------------------------------------------------------------------
nowfal's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1000
View this thread: http://www.excelforum.com/showthread.php?threadid=47777

0
10/23/2005 6:19:02 PM
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" <nowfal.1xdcqb_1130094301.8714@excelforum-nospam.com> wrote in
message news:nowfal.1xdcqb_1130094301.8714@excelforum-nospam.com...
>
> hi,
> Anybody can help?
>
>
> -- 
> nowfal
> ------------------------------------------------------------------------
> nowfal's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=10003
> View this thread: http://www.excelforum.com/showthread.php?threadid=477776
>

0
ragdyer1 (4060)
10/23/2005 8:44:14 PM
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?action=getinfo&userid=10003
View this thread: http://www.excelforum.com/showthread.php?threadid=477776

0
10/24/2005 9:58:49 AM
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" <nowfal.1xeied_1130148305.5878@excelforum-nospam.com> wrote in
message news:nowfal.1xeied_1130148305.5878@excelforum-nospam.com...
>
> 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?action=getinfo&userid=10003
> View this thread: http://www.excelforum.com/showthread.php?threadid=477776
>

0
ragdyer1 (4060)
10/24/2005 8:00:46 PM
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?action=getinfo&userid=10003
View this thread: http://www.excelforum.com/showthread.php?threadid=477776

0
10/25/2005 10:16:41 AM
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 Wrote:
> Hi Ragdyer,
> Thanks for reply. I also thinking that way. Hop
> somebody will have some new tricks.
> nowfa

--
Bryan Hesse
-----------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2105
View this thread: http://www.excelforum.com/showthread.php?threadid=47777

0
10/25/2005 12:41:48 PM
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

--
nowfa
-----------------------------------------------------------------------
nowfal's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1000
View this thread: http://www.excelforum.com/showthread.php?threadid=47777

0
10/25/2005 6:12:36 PM
Reply:

Similar Artilces:

Creating a group of cells. Need Help Please.
Havn't used excel in a while and I need to create a group of cell corresponding to an input of a min and a max. Here are the details. On one sheet I have a box where you enter th min and a box where you enter the max. In another sheet I want column starting at A2 to output (MIN,A2+1000,A3+1000,....MAX) ho would I do this -- Thundersix ----------------------------------------------------------------------- Thundersixx's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3055 View this thread: http://www.excelforum.com/showthread.php?threadid=50207 Name the...

Payroll Batch Number
Is it possible to obtain the batch number of a transaction after it has been posted? I have been asked to create a report for our payroll department that lists the batch number in addition to the transaction detail that is contained in UPR30300. Thank you, Jeff Majchrzak No, the batch number is not available for reporting. -- Charles Allen, MVP "Jeff Majchrzak" wrote: > Is it possible to obtain the batch number of a transaction after it has been > posted? I have been asked to create a report for our payroll department that > lists the bat...

Excel 2003 extract repeating cells
I have successfully sorted my data to show repeating entries in relation to two specific columns. I can't seem to figure out how to select these repeating entries (without doing it manually, of course) and putting them either into their own column(s) or an entirely different spreadsheet altogether. The goal is to save time in managing THOUSANDS of documents in this manner so that my colleagues can easily pick up repeating entries and take according action. Doing it manually is very time-inefficient. Nit Wit, You don't really describe enough of your layout or what you actually mean...

cell looses name after sorting
Can someone help me with the following problem in Excel 2000: in a table I have attached serveral cells with unique cell names, the values in these cells are used in other sheets. the problem is that when I sort the table, the cell names stay in the original rowposition; they are not sorted! while their values are. So Cell names get different values, and other calculations on my other sheets get messed up! How can I make the cell names relative instead of absolute? thankx in advance, Jim --- Message posted from http://www.ExcelForum.com/ "jimfx >" <<jimfx.109zcv@exc...

How can I wrap text across merged cells?
I'm using Excel 2000. I have a set of merged cells A5- E5. I have several lines of text in the cells and I want them to wrap across the merged cells and it is not working. All I get is the first line of text showing and the rest is cut off. It works if the cells are not merged, but I really need to do it in my merged cells. Is there a way to this other than manually resizing the height of the row? Instead of merging cells, have you tried the "Center Across Selection" option ? The appearence is just about identical to what you would get using merged cells, although I d...

chart label reference based on the column number
In a worksheet with an embedded chart, I have a cell, S4, where I enter the number of the column I want to chart (these numbers are listed as labels in cells A2:R2). I can't figure out how to translate the number in S4 into the corresponding column letter. For example, if S4 contains "3", the chart title should be =$C$2. I think working with absolute references, R1C1, might be easier here, but somehow what I've tried, didn't work (the entire spreadsheet is based on relative addresses (A1). z.entropic in a cell (Z100) put =index(A2:G2,1,S4,1) Change G2 to whatev...

Custom cell formatting
I need to create a custom format for a series of cells that will begin like this. I can't figure out what the code character is fora volitile potentially alpha character. Can anyone help me?? -Monica, Dallas 000000 000001 000002 .... 000009 00000A 00000B 000010 000011 ...... Monica something like this might work for you but you'll need to put all the leading digits in for the entry with the alpha character Regards Trevor "MDavison" <davison@fr.com> wrote in message news:#SD0tUzTEHA.1652@TK2MSFTNGP09.phx.gbl... > I need to create a custom format for a series ...

Embedding worksheet into Word
Has anyone had the experience where when they embed a worksheet into Microsoft Word, a portion of the worksheet gets cut off? Is there an easy fix to this? ...

Auto formatting features: How do I align page numbers in publicati
I've got a problem with my publication. I can't align even page numbers to the left without automatic moving the odd numbers to the left as well? Can someone help me? Cissy99 wrote: > I've got a problem with my publication. I can't align even page numbers to > the left without automatic moving the odd numbers to the left as well? Can > someone help me? You need to create a two-page master rather than a one-page master. What version of Publisher are you running? -- Ed Bennett - MVP Microsoft Publisher http://ed.mvps.org ...

Fewer normal template problems with Word 2000 or 2002?
I have enjoyed some things about Word 2007, but I keep having problems with macros and templates. I am wondering if (a) macros increase the likelihood of corrupting the normal template in 2007, or (b) if the older versions of Word had as many template problems. I have used Word in 2000, with Office 2003, and now with Office 2007 and I don't ever recall having as many problems as I've had with 2007. What I'm thinking is that if it is simply a macro problem causing corruption, I would simply use 2007 without the macros (they're nice but I can live without them). On the...

Send from Word
When I hit the send button in Word or Excell I get a pop up that say choose profile with Outlook in the drop down. That is the only name in the drop down. If I hit OK I get the following Message. The MAPI Spooler could not be started. Close and then restartall mail-enabled applications.MAPI 103 (0000004C7) If I choose to set up a new profile I get a Microsoft Exchange Setup Wizard. As I try to setup a new profile I come to the following, The following unrecoverable error has occured: The .DLL file for this service is either damaged or could not be found. Any ideas? Doug, What versi...

Unable to repy using word editor
When repyling to an e-mail I receive the following message, "This form requires Word as your e-mail editor, but Word is either busy, or cannot be found. The form will be opened in the Outlook editor instead". I found article 284900, that states you may get this message when you have office 2000 and office xp installed on the same computer. This is not the case on my computer, Office 2000 does not exist, only office xp. Can anyone help me? There appear to be a whole host of causes for this error message. Most have no solution. Here are a few that do: http://support.microsoft...

Locking cell color while allowing data changes in cell
In excel 2000, I created an attendance worksheet for my classes.(Alphabetized names down left vertical column. Dates across top of horizontal row.) I added a different color to all cells in every other row to make for easier reading of each student's name and absences. Every other row stays with a white background. My question: I wondered if it was possible to lock row colors while allowing data to change on top of them. If a new student is added to my class in alphabetical order, the alternating color pattern is often lost. It is a pain to rechange row and cell colors. Any shortcut ...

Unicode "private use" glyphs in Powerpoint and Word 2008 #2
Office 12.0.0.1 on Leopard 10.5.2, all updates applied to both. A friend is migrating from Windows to Mac, and has come across something that we're trying to work around. He has some .ppt files with font glyphs in from a Unicode .ttf font (a specific purpose font for showing the LCD display characters on a piece of monitoring equipment). When we bring the font and .ppt over to the Mac, the glyphs from the normal ASCII range come over into Powerpoint 2008 fine, but the glyphs from the "private use" range from F000 upwards a way don't transfer. In Powerpoint they show as spac...

Formatting Cells in Excel 97
Hi Guys, Sorry if I sound real stupid but is there anyway that we can control the column formatting in Excel 97 like let's say column A = GENERAL(6), column B = GENERAL(4), column C = TEXT(18) etc? Appreciate any form of advice, thanks!! I replied in the programming group. Are you seeking a programming answer? It's usually best to only post to one group, and include the remark "Please tell me if I should ask this in another group." On Thu, 28 Aug 2003 00:56:47 -0700, "Daryl" <daryl.ho@tnt.com> wrote: >Hi Guys, > >Sorry if I sound real stupid b...

Getting Cell Value from the Concatenate formula
I built a concatenate formula that returns the following result: =Jul! $D27 I am looking for the cell contents of Sheet: July Column D Row 27. I tried to use offset, but I am stumped. Can I add something to the front of the concatenate to not only build the reference to the cell, but also return the value instead of the =Jul!$D27 ? Thanks John =indirect(yourformulahere) Don't include the equal sign in your formula. And match the name correctly (Jul or July???). Depending on the name of the worksheet, you may need to have a string that looks like: 'Sheet 99'!d27 =indirec...

Excel devides every number by 100
Even when I open a brand new file. I input a number (ie 5) and get 0.05 back. I have tried doing everything in cell format etc.. anyone have an idea? it is very annoying and adding loads of work to my project. thanks "=?Utf-8?B?a2luZ3M=?=" <kings@discussions.microsoft.com> wrote in news:48A39EFC-2D09-420F-B2DF-6EF5C0DB6109@microsoft.com: > Even when I open a brand new file. I input a number (ie 5) > and get 0.05 back. I have tried doing everything in cell > format etc.. anyone have an idea? it is very annoying and > adding loads of work to my project. > ...

find match then change cell value
In column A I have cells filled with text and in column B I have cells filled with numbers. I need to check if the number in cell C1 equals any of the numbers in column B. If a match is found then I need to change the text in column A to CBO. e.g. Column A Column B Column C aep 5 7 apa 0 gci 59 xto 5000 xle 7 oih 253 ed 8 Since the cell C1 = 7 equals the 7 from column B, I need to change the data in column A from xle to cbo. Is this possible...

Getting same number to appear over and again
Hi. I'm trying to change the E's I have entered in a column to 1's. However, in lieu of changing them one by one, I would like to do them all in one fell swoop. Any suggestions? John Hi John You could use find and replace. Select all of your data. Goto Edit>Find Find what:- E Replace with:- 1 HTH Martin John Do NOT multipost. See your answer in Excel group. And do a google search for newsgroup etiquette!! John Do NOT multipost. See your answer in Excel group. And do a google search for newsgroup etiquette!! John Do NOT multipost. See your answer in Excel ...

Repeat statements "x" number of times...
Hi All, I have the following code at the end of a long macro: Columns("D:D").Find(What:="total").Select ActiveCell.Offset(0, 4).Range("A1").Select ActiveCell.FormulaR1C1 = "=Sum(RC[-1]-RC[-2])" ActiveCell.NumberFormat = "0.00" ActiveCell.Font.Bold = True With ActiveCell.Interior .ColorIndex = 6 End With What I am doing is when the word "total" appears, it will go to the cell 4 columns to the right, and then do a formula and format the cell. Question: How can I get this to repeat itself for eac...

Detect cell colour
I have spreadsheets where 5 cells have general titles and have different background colours. In other sheets, dozens of cells list topics and are also colour coded to show which general title they refer to. I need to tabulate information so that a row would contain three cells: the general title, a topic and data about it. Is there a command that returns the colour of the cell background, so that I could use this to select the correct title to accompany the topic? Hi, Yes there is: FontC = Worksheets("Planning").Cells(<Row>, <Column>).Font.ColorIndex Interi...

Word doc format
I tried to email a word attachment written on office for mac 2008 home version, but the recipient couldn't open the doc. recipient using windows xp. rlciii@officeformac.com <rlciii@officeformac.com> wrote: > I tried to email a word attachment written on office for mac 2008 home > version, but the recipient couldn't open the doc. recipient using windows > xp. If they don't have a recent version of Windows Office, they may not be able to open a .docx file. Choose File > Save As and save as a .doc file. -- Adam Bailey | Chicago, Illinois adamb@lull.org | Finge...

sort special text/numbers in format with many dots
Hi I need your help with sorting in Excel! I have mani Text fields with numbers into it. As example: 1 5.1 1.2 10.2.1 1.2.3 2 2.1.5 3 3.2.1.1 3.3.1.1.1 And it should sorted like this 1 1.2 1.2.3 2 2.1.5 3 3.2.1.1 3.3.1.1.1 5.1 10.2.1 How can I sort this like numbers? My problem is, that not all Numbers have the same format as x.x.x.x! And I can't change this Text-Fields to Numbers, because 10.6.1 looks the like 37052 :-( Any suggestions? Thx Marco Hi With your data in column A, insert a blank column at B. In B1 enter =IF(ISNUMBER(FIND(".",A1)),LEFT(A1,FIND(".&q...

revision lines on Word
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I need to show when text is revised by inserting a line in the left margin. This works ok on text, and has worked with tables and ToC in past versions (2004 for Mac). Now in the new version it causes formatting chaos. <br> I draw a line in the left margin of a page with a table and when the mouse is released the line a) disappears, or b) jumps up to the previous page, or c) jumps over into the table and displaces the contents downward off the page. <br> I am not using the Track Changes feature. &l...

How do I format cells to a specific number of digits?
I am trying to format a column so I can enter 5 digits only. It has between 5 and 7 digit numbers in it right now and I need to delete the remaining ones. It is about 350 numbers, so it would be a lot of work to go into each cell and delete the remaning numbers. Is therea way I can format the whole column to allow 5 digits? The same happened with letters. I had codes in it that consisted of numbers and letters and the client only wants the first two letters to remain. How can it be formatted so only 2 characters are allowed to keep me from having to enter each cell separately? Hi form...