#### adding cells which include numbers and text

```Hi, I am hugely ignorant regarding excel, no training, and apparrently
no brain... What I need to do is allocate a number to a placing i.e.
1st = 100, 2nd = 99, 3rd = 98 etc. If A5 (for example) has 1st in it, I
put in A6: =101-A5, but it will not add it because of the text. How do
I get the formula to ignore the text in A5???

Shelley ;)

```
 0
10/25/2005 4:40:31 AM
excel.misc 78881 articles. 5 followers.

6 Replies
277 Views

Similar Articles

[PageSpeed] 26

```Hi!

One way:

=101-SUBSTITUTE(A5,RIGHT(A5,2),"")*1

Biff

> Hi, I am hugely ignorant regarding excel, no training, and apparrently
> no brain... What I need to do is allocate a number to a placing i.e.
> 1st = 100, 2nd = 99, 3rd = 98 etc. If A5 (for example) has 1st in it, I
> put in A6: =101-A5, but it will not add it because of the text. How do
> I get the formula to ignore the text in A5???
>
> Shelley ;)
>

```
 0
biffinpitt (3172)
10/25/2005 5:03:16 AM
```Hi Biff,

Thanks!  Works the answer out great, but merges two cells together
where the answer is (using my example, it merged A6 and A7) - what am I
doing wrong?? :(

Shelley ;)

```
 0
10/25/2005 8:13:31 PM
```Hi!

Not sure I understand what you mean by: "merges two cells together where the

???

Biff

> Hi Biff,
>
> Thanks!  Works the answer out great, but merges two cells together
> where the answer is (using my example, it merged A6 and A7) - what am I
> doing wrong?? :(
>
> Shelley ;)
>

```
 0
biffinpitt (3172)
10/26/2005 12:39:59 AM
```Hi again Biff,

I clicked on the merge & centre button and it re-split the cells, then
when I copied the cell containing the formula, it didn't merge any
more...

Shelley ;)

```
 0
10/26/2005 1:03:27 AM
```Hi,

I posted my last comment before I saw your one - When I copied your
formula into A6, it calculated the answer correctly, but merged that
cell with A7.  Does that make more sense?  Anyway, I don't know how I
got it to do that, but it's OK now (see last reply).

Shelley ;)

```
 0
10/26/2005 1:46:03 AM
```Ok, glad you got it straightened out!

Biff

> Hi,
>
> I posted my last comment before I saw your one - When I copied your
> formula into A6, it calculated the answer correctly, but merged that
> cell with A7.  Does that make more sense?  Anyway, I don't know how I
> got it to do that, but it's OK now (see last reply).
>
> Shelley ;)
>

```
 0
biffinpitt (3172)
10/26/2005 2:53:27 AM

Similar Artilces:

CRM cannot see all users from AD
Hi I�m new to CRM and when I am trying to add new users to CRM from our 2003 AD, then I only see about 70% of all our AD users. I think that the users I dont see are old users that have been migrated from another NT domain a few years ago. Or if I create new user in AD and go to CRM and try to add the new user, I dont see him. 1) Do CRM connect to AD every time I will add a new CRM user or does this happens once a day? 2) How can I check what is different between an "old" migrated user and "new" user that been created only in 2003 AD? Is there a tool? Tomppa Hi ...

all grids to display the total number of records returned by the q
Currently only first (up to) 100 records are displayed with an indication that query results contain more pages, the only way to see how many records are in the list is to click next page button multiple times (if the total number of records is not too high). The customer is asking to have the bottom statusbar that shows "100 records shown" to show the total number of records returned by the view (or the search) query, e.g. records 201-300 of 2,456. Thanks, Sergei ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most ...

How to un-hightlight the text in Rich Edit Control?
Hi all, I have a read-only Rich Edit Control in dialog and insert text into it via code. When the dialog pops up, all the text in Rich Edit Control are highlighted as they are selected. How to un-hightlight them by code? Thanks in advance. Jerry scdong wrote: > Hi all, > I have a read-only Rich Edit Control in dialog and insert text into it > via code. When the dialog pops up, all the text in Rich Edit Control > are highlighted as they are selected. How to un-hightlight them by > code? Thanks in advance. > > Jerry Quick & Easy, moving the caret should do the trick....

Here Goes...long story short, I am trying to make Excel work a little like Access. On first tab of my worksheet labeled "All" is all my data. Additional tabs contain links of the data from the 'All' tab1 (Party Invite-tab 2, Golf Invite-tab 3, Alumni-tab 4, etc.) However...when I sort the data on the 'All' tab, the links pasted on the other tabs continue to reference the pre-sorted ROW/COLUMN from the 'All' tab instead of the post-sorted DATA. Therefore data from the links on the other tabs are useless to me. Is there a formula to link the DATA in t...

Text color change in a cell depend on the cursor position elsewher
Excel. I want either the background color or the text color of a cell, or cells, to change when the cursor is in a particular position. e.g. The background/text color change for the far left cell of a row when the cursor is anywhere in that row. Hi! Two things: 1. You can get a cell to change colour with a VBA routine such as: Private Sub Worksheet_SelectionChange(ByVal Target As Range) r = Target.Row Cells(r, 1).Interior.ColorIndex = 6 End Sub This paints the first cell in the row yellow. But you'll need to give some thought as to whether/how you will dea with moving off that ro...

Reusing SBMnn Numbers and Locating SBM files
I have added and deleted various companies in SBM. Each add uses the next SBM number. When I delete a company that number is removed. Is there a way to recover and re-use a number? Is it possible to have different SBM companies on different drives? Fred Fred, Next newsgroup down. This is the M\$ Retail Management Point of Sale group. -- * "Fred" <Fred@discussions.microsoft.com> wrote in message news:A2454A5E-956F-4EF1-BCDA-A5A4B8C0457B@microsoft.com... I have added and deleted various companies in SBM. Each add uses the next SBM number. When I delete a company that numbe...

Footnote in a Formula Cell
I am wondering if there is a way to add a footnote to a cell tha contains a formula. Everytime I try it gives me an error. I canno change the cell to a value beause the forumla in that cell is linked t another workbook. Please HEEELP -- Message posted from http://www.ExcelForum.com Learned this from MVP Debra Dalgleish: http://tinyurl.com/6rnm2 HTH Jason Atlanta, GA >-----Original Message----- >I am wondering if there is a way to add a footnote to a cell that >contains a formula. Everytime I try it gives me an error. I cannot >change the cell to a value beause the foru...

Renaming Cells linked to Pivot Tables
Hey guys, Is there a way to rename the field heading that are linked to th pivot tables, so when I click update data, it just takes on the ne name. Right now if I just rename the heading, the pivot falls apart.... any ideas? Thanks! :confused -- gsimmons200 ----------------------------------------------------------------------- gsimmons2005's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2638 View this thread: http://www.excelforum.com/showthread.php?threadid=46689 click on the title of the row/column in the pivot (so the data is not highlighted) and chan...

VBA to write cells out to a file
I want to... open new file check cell data (If data="xyz" then goto close file or whatever) write cell data write a string move to next cell check cell data (If data="xyz" then goto close file or whatever) write cell data write a string etc close file can anyone give me sample code snippit to... - create/open a file - check cell data - branch on results - write cell data - write a string - close file thanks for any help You can use the macro recorder to create some of this code as you perform the steps manually. Then, edit the generated code. If you...

Can you have fixed cell reference when dragging/copying formulae?
When you drag or copy a formula which refers to another cell, then that cell's reference is updated when the formula is dragged or copied across a spreadsheet. Is it possible to 'fix' the reference of the outside cell such that the formula refers to a fixed cell irrespective of where the formula is copied to? For example a formual in cell B2 containing a reference to cell A1 will always refer to the cell immediately above and to the left when copied elsewhere. Is it possible for the formula to be forced to refer to cell A1 irrespective of where it is copied? You can use \$a\$...

Spell Number
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) I have a check register spreadsheet I created in Windows version of Excel that can print checks. It includes a VB function that takes the numeric dollar value (e.g. \$100.25) and spells out that number on the check (e.g. One Hundred Dollars and Twenty Five Cents). How can I do this on my Mac? On 5/4/10 7:26 PM, mrcdqtt@officeformac.com wrote: > Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) I have a > check register spreadsheet I created in Windows version of Excel that > can print checks. It include...

font color depends on a number
I have a number in A1 :the font color=black If I place a "1" in B1 I want the font color in A1 to turn to RED and consequently place a "2" in B1 would return tje font color to black Please help & Thanks What if B1 is neither 1 or 2? If we assume that B1 will always be 1 or 2, select A1, do format>conditional formatting, in the formula is box type =B1=1, click the format button, select fonts and a red colour, click OK twice. If you have Excel 2007 post back -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com ...

Including Video Files
Hi, I've made a Web site using Publisher 2007. Everything works except my three WMV video files. How do I include them in my Publisher file so that when I "Publish to Web..." they get FTPd to my site and the links to them work? Thanks, Dan Including external files in a Publisher web: http://msmvps.com/blogs/dbartosik/archive/2006/01/07/80561.aspx DavidF "Daniel Jameson" <danjam47@newsgroup.nospam> wrote in message news:%23ObKak9cIHA.3400@TK2MSFTNGP03.phx.gbl... > Hi, > > I've made a Web site using Publisher 2007. Everything works except ...

Prevent changing PO number sequence from PO Entry screen
In Purchase Order Entry, if a user enters a PO number which does not exist as an open or historical PO and which is lower that the current Next Number in Purchase Order Processing Setup, the Next Number is changed to the lower number even if the user deletes the PO without saving. Obviously, this wrecks havoc with PO number sequence. The system should not allow the Next Number sequence to be changed from the PO Entry screen. Ideally, there would also be an option to prevent the user from overriding the next PO # similar to what is available in SOP document setup. ---------------- Th...

The form required to view this message cannot be displayed. Contact your help ad
I'm trying to open email that contains forwarded email's. I'm using Outlook 2002 w/SP-2 and XP Pro on clean installs. I can save the attachments but would like to just display them if possible. ...

How do I calculate cells across Excel 2003 workbooks?
I have three different workbook files stored in the same folder and I want to have the three worksheets in each of the three workbooks totaled in a new workbook file. I am trying to create a quarterly report from three months of data. All data is located in the same cells in the worksheets in each of the workbooks. If the different workbook files were all in the same workbook, it would be a lot easier. Can that be done? "mj" <mj@discussions.microsoft.com> wrote in message news:99DDF3D0-2951-4FEE-978B-CCA5BE40D955@microsoft.com... >I have three different workboo...

Text Columns
I have to columns, alphabetically sorted, one with 1oo names, one with 8o-9o names, which are identical to the ones in the column with 1oo. Is there a way (formula or macro) that places the identical text (names) next to each other in the same row, leaving blank the cell where there is no match? The columns should then look like this: Miller Miller Smith Kerry Kerry Ferguson Ferguson Peters Black Black etc. Thank you Say 1st column is column A, 2nd column is column B. Enter in C1: =IF(ISNA(VLOOKUP(A1,B:B,1,0)),"",VLO...

Help!, Adding Custom Forms in CRM
HI Mike, I guess, you have very good exposure to the forms customization in CRM. Could u please help me with this issue. I have a list of products in a seperate database outside CRM. The quantity on hand field in that database is real. Now what I really need is to have a button or toolbar item on the OrderProducts form to Check for availability of the product. I do have the product GUID in the external table. I will have get the currently selected product's guid from orderproduct screen and pass this to a custom aspx page. This custom aspx page will find the available qty and sh...

BUGGY TEXT BOXES IN GRAPHS
I am using Excel 2000, version 9.0.6926 sp3 Seems like every time I use a text box on a graph, or try to opaque its background, or change outline features, my excel crashes and closes down, losing everything done since last save event. At first I thought it was one corrupt file- but then I recreated the problem on a new machine, and a new spreadsheet file. Does anyone know if this bug has been taken care of- or is there a fix to it? Thanks Paul ...

how do i delete cells or duplicate cells in a table ? #2
I went to make a table with 8 colums and 3 rows, which turned out fine..but now i need to make 3 more rows added on but half the size long with only 2 colums.. so i figure i can just duplicate or copy from my table 3 of these rows and shirk it and delete 1 cell...but trying to get help on how to add or remove cells seems the hard part You could create a new table with your options and add it to the old, or add three rows to the old and merge two of the cells. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "james" <...

Referencing every nth cell in a new column
I have data in E4, E12, E20... that I want to copy into a new column without the extra rows in between. How can I do this? So far I've tried entering =E4 in the first cell, then =E12, and then using the fill handle. Didn't work :( I'm not very good at excel so please be specific if you talk about coding or heavy duty formulas. > I have data in E4, E12, E20... that I want to copy into a new column without > the extra rows in between. Here's one way. If the new column starts at row 1, put this at the top and copy downward: =OFFSET(\$E\$4,(ROW()-1)*8,0...

Non-number value in Charts
I am trying to make a non-number value show up in a column chart. The chart is automatically showing any non-number value as zero. This is fine, however I would like the Display Data Labels to show the value that is displayed in the cell. How do I do this? I think you're actually wanting to display either series name, or category name. If you just want custom labels on individual data points/columns, you'll need to manually edit the labels yourself by selecting the value, then clicking again (aka, a slow double click). -- Best Regards, Luke M *Remember to click "yes&qu...

How do I separate address information from within an Excel cell?
I have a Christmas Card list that was created using a single cell for the whole address. Street, City, Zip/Postal etc. I need to split those components out into separate cells in order to do a proper mail merge. Is there any way to do this. I am not completely unfamiliar with macros but I if that is the solution I would require a fair bit of hand holding. Thanks S. If you actually used commas to separate the fields (and none of the fields contain commas), you could select the column and do: Data|text to columns delimited comma Remember to keep enough open columns to the right s...

Text/Font Size in Money 2006
Dude, there has to be a way to make the font size bigger in Money 2006! My eyes are stinging! I've looked everywhere but can't seem to figure out how to make the text easier to read. Why does Microsoft think that super small text size is better? I'm under 30 y/o, don't wear glasses, and I'm running my 19" LCD at 1280 x 1024 (which I don't think is too outrageous). I have clear type font smoothing enabled and I have even bumped up the font size within Windows XP. Do I need to get a magnifying glass next? ---------------- This post is a suggestion for ...

Indent formatted cell
I would like cells that are formatted with comma (no decimals) to be indented. I can do it to non-formatted cells, but once I apply the currency or comma style, I can no longer indent the cells. I wish to do this to line them up closer to the centre and thus under the heading at the top. Thank you Deborah Hi, Deborah- You don't specify what your comma format cells look like so I don't know if this will work. How about if you leave the cell unformatted, and achieve the comma formatting with the TEXT() function. Then you can apply the indent to the cell. Hope this does it for you...