cells of varied length extract last 3 digits

each cell in a column ends with "Program ###".  I would like to create a 
column with just the ###. Where ### represents varied digits.
Thanks
Peggy
0
Utf
11/28/2009 3:41:01 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
1205 Views

Similar Articles

[PageSpeed] 18

Use the formula for retreiving the last three characters / digits from a 
particular cell.

For Retrieving Last Three Text Characters from a cell
=TRIM(RIGHT(A1,3))

For Retrieving Last Three Numeric Values from a cell
=--TRIM(RIGHT(A1,3))

Change the cell reference to your desired cell, if required.

If this post helps, Click Yes!

--------------------
(Ms-Exl-Learner)
--------------------



"PeggyT" wrote:

> each cell in a column ends with "Program ###".  I would like to create a 
> column with just the ###. Where ### represents varied digits.
> Thanks
> Peggy
0
Utf
11/28/2009 4:02:01 PM
> For Retrieving Last Three Numeric Values from a cell
> =--TRIM(RIGHT(A1,3))

A1 = text program 001

Your formulas return 1.

If you want the number extracted as a numeric number and to retain the 3
digit format then you'll have to use a custom number format like 000 to
display the leading 0s. Note that when doing this the leading 0s are for
display purposes only. The true value of the cell will be numeric 1.

-- 
Biff
Microsoft Excel MVP


"Ms-Exl-Learner" <Ms.Exl.Learner@gmail.com> wrote in message 
news:40163A66-DD43-4B72-B6CB-6CCE843D7A33@microsoft.com...
> Use the formula for retreiving the last three characters / digits from a
> particular cell.
>
> For Retrieving Last Three Text Characters from a cell
> =TRIM(RIGHT(A1,3))
>
> For Retrieving Last Three Numeric Values from a cell
> =--TRIM(RIGHT(A1,3))
>
> Change the cell reference to your desired cell, if required.
>
> If this post helps, Click Yes!
>
> --------------------
> (Ms-Exl-Learner)
> --------------------
>
>
>
> "PeggyT" wrote:
>
>> each cell in a column ends with "Program ###".  I would like to create a
>> column with just the ###. Where ### represents varied digits.
>> Thanks
>> Peggy 


0
T
11/28/2009 7:06:45 PM
Reply:

Similar Artilces:

CRM 3.0 on Outlook 2007
Hello, I installed the spanish version of CRM Client on my Outlook 2007. I noticed the user interface has changed. With this version any time I click on a CRM entity it pops up an Internet Explorer window. Is this normal or should I still be seing the data On oultook application (that was the case of CRM for Outlook 2003)? Regards, Irene Nothing changed (assuming you are reffering to the v3c update) in this regard. The CRM for Outlook client has typically opened browser pages. The only exception was if you double clicked on the core Outlook items. Basically, if you click from an O...

DAO is less relevent than Wnidows 3.1
why do you diptards still use DAO again? why would you want to have 2 different DIALECTS of SQL? Anyone using MDB or DAO should be fired and then spit upon " S u s i e D B A [ M S FT ] " <s u s i e d b a @h o t m a i l.com> wrote: >why do you diptards still use DAO again? > >why would you want to have 2 different DIALECTS of SQL? > >Anyone using MDB or DAO should be fired and then spit upon Note that this person is really A a r o n K e m p f and that he is not an employee of Microsoft. Tony -- Tony Toews, Microsoft Access MVP Please respond onl...

Cells print so small I cannot read numbers. How do I fix?
I have been working with page break. Now I have the grid on 1 page..but it is far to small to read. now when I try to spread it back to 2 pages, it just takes the same tiny microscopic type and spreads it into 2 pages. I am stuck printing tiny type. How can I get the grid cells back to a size that is readable. It sound like you have selected Fit to 1 page in File > Page setup > Page > Scaling. Either select to fit it to 2 pages or select Adjust to 100% size -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Repl...

When I am in one cell highlight another
Hello, When I am in one cell highlight another. Lets say that if my cursor is in A1 I want D1 highlighted or with another cursor on it. Then if I move to A2, D2 should be the one with another cursor or highlighted. Thank you for your help, Jose Juan Diaz hi, Jose Juan ! > When I am in one cell highlight another > ... if my cursor is in A1... D1 highlighted or with another cursor on it. > ... if I move to A2, D2 should be the one with another cursor or highlighted. 'put' a cursor on non-active-cell... [I don't think it's possible] :( to highlight 'D' wh...

Process all cells in a (user) selection
I am using this code to give me cell by cell access to a code defined selection; Dim aCell As Range For Each aCell In Sheet4.Range("A4:A34") ' Do Stuff Here Next aCell which processes every cell in the A4:A34 Range How do I do the same thing for a selection drawn by the user before pressing my 'Process' button ? Thanks On 05 May 2010 11:10:21 GMT, Isis <isissoft@NOSPAMbtinternet.com> wrote: >I am using this code to give me cell by cell access to a code defined >selection; > >Dim aCell As Range >For Each aCell In S...

last 4 wk average using calculated pivotitem
Hi, I am trying to create a pivot table/chart from data that is listed b week. So my table has "week" as the row field and "total X" as th column field. I want the chart to display the total for each week as bar (the easy part) and also to graph the average of the last fou weeks as a line on top of the bar graph. I have tried to figure ou how to add a calculated pivot item but I can't seem to get it to wor right. So for each week there would be a second almost subtotal lik entry that calculates the average for the previous four weeks (if ther aren't four previo...

contact body to description sync, ntext length
Hi, in our company we are currently using the outlook "body" field to record a lot of contact information. When we try to sync the contacts with crm, we get the message that the information in one or more fields is to long. We already raised the length of the description field (type: ntext) from 2000 to 5000 (which is the max value) but this does not seem to change anything. To put this clear, 5000 should be enough, but the sync process seemingly stops at 2000 every time, regardless of the field length entered. Is it possible to tweak the ntext length that the sync process can ...

think cell program
is anyone familiar with a program called think cell? Any thoughts? (powerpoint v 2003). Is this an add-in? thanks Sara It's very good. You can "try for free" from their website: http://www.think-cell.com/ Recent interview with one of the founders on Indezine: http://blog.indezine.com/2009/12/think-cell-conversation-with-markus.html -- Echo [MS PPT MVP] http://www.echosvoice.com What's new in PPT 2010? http://www.echosvoice.com/2010.htm Fixing PowerPoint Annoyances http://tinyurl.com/36grcd PowerPoint 2007 Complete Makeover Kit http://tinyurl.com/3...

DSUM #3
[Excel 2003] I have a worksheet that looks something like this: A B C Charge Payable Paid $100.00 Y $650.25 Y $ 25.00 N 10/1/2007 $ 85.75 Y I would like to place a formula in a cell that will return the sum of all expenses in column A for rows which have a "Y" in column B and no value in column C. I think DSUM is the function to use, and I have read several help pages on it, but no formula I have tried has actually worked. I would appreciate any help. Try this: =SUMPRODUCT(A2:A10...

User status in Sharepoint 3.0
I see there is a user status that works with Office Communicator. Is there away to do this with out office Communicator or free software to do this? We are a small company with about 10 users. Nothing I'm aware of. This is built in integration with the OCS platform. -- Daniel A. Galant Imagine what we could be... if we could just imagine. "todd" <todd@discussions.microsoft.com> wrote in message news:089333D7-F0D4-4994-87E3-74A387AE33D0@microsoft.com... > I see there is a user status that works with Office Communicator. Is > there > away to...

Please help with last formula for order form.
I am able to accomplish this with 1 column by the formulas below. Cell H160 is the subtotal: =IF(SUM(H72:H111)>0,SUM(H72:H111),"") Cell H166 the total: =IF(SUM(H160)>0,SUM((H160*H163)+H160),"") Cell H163 is for Tax. I am almost finished creating an order form. I would like to get the SUM of 3 different columns that are separated. I am not able auto fill strait down the column, because the information is separated in groups with titles, and the cells are not identically sized. I tried varations of this formula: =IF(SUM(H72:H111)+(116:131)+(135:154)>0,SUM ((H72:H...

Newbie Cell Reference Question...
Hello, I apologize if this question has been answered before, but I'm no quite sure what to search for as I'm not very good with excel. I'm making a spreadsheet where one sheet references another. I'll tr to explain this as best as possible.... I'm creating a spreadsheet for a fantasy basketball league. I have "Data" sheet that contains data for all players. I have another sheet "Teams" that has all the players on each team. Column B contains th players name, and column C contains a number that corresponds to th row this player is on in the dat...

extraction code from celd
Hi :) I have a Excel problem :confused: MY QUESTION IS: if A5 = "JhoN FreD SmitH ChonG then A6 = "JNFDSHCG" I would like have got solution ;) please !!! thank you ver much nando4000@latinmail.co ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com You could create a User Defined Function: '=============================== Function GetCode(rng As Range) As String Dim i As Integer Dim str As String i = Len(rng.Value) For i = 1 To i str = Mid(rn...

upgrade #3
Hello, I just received an e-mail from my bank which states they will no longer be supporting MS Money 2000 or 2001. I currently have Money 2000. I am looking to upgrade, however do NOT wish to lose any of my financial data or the ease of epaying my bills. When I upgrade, do I delete the old MS Money and leave the *.mny file ? Or do I complete an install and let Money 05 remove the old MS Money ? thanks for your help, Jason Already replied to. "dejaking" <dejaking@discussions.microsoft.com> wrote in message news:BB6DD057-70DF-404A-AD65-CE0E914B366D@microsoft.com.....

Cell background shading in Excel
Why is it that the background shading colors available under the Paintbucket Icon chjange from file to file? Also, how can I add some of the colors to the paintbucket that are availabe under the format/cells/pattern pulldown? Colors are a Workbook-level property, so you can change the color palette for any workbook. Choose Tools/Options/Colors... and modify the colors to your hearts' content. You can also import another workbook's color palette from that dialog. Note however, that there are only 56 bins in the color palette, so you can only use 56 colors at a time. The paint b...

Sum every other cell?
I own a used-book shop. I have an Excel SS to track how many books pe day I sell in each of 28 categories and the $$$ I take in for eac category. So there's two columns for each day: #books & $$$. therefore want to add every odd-numbered cell in a row to get the tota number of books for a category in a given period of days and ever even-numbered cell in the same row for the the total $$$. Is there simple formula or function for this -- bookmanj ----------------------------------------------------------------------- bookmanjb's Profile: http://www.excelforum.com/member.php?acti...

Outlook Express #3
Where does Outlook Express store received email messages? I'm trying to setup a new HD and don't want to keep booting to different drives to export data. In article <0f0801c34676$c8efcd70$a001280a@phx.gbl>, jkstueve@nktelco.net says... > Where does Outlook Express store received email > messages? I'm trying to setup a new HD and don't want to > keep booting to different drives to export data. > Tools-Options-Maintenance-Store Folder. BTW, FYI this group is for the support of Outlook 97, 98, 2000 and 2002, NOT Outlook Express, which, although similar...

MERGE CELLS
I have Name, PO Box, street address, city, state, zip across a row in 6 seperate cells/columns. I want to have this format in 1 cell: Name PO Box Street address City, State Zip STEVE wrote: > I have Name, PO Box, street address, city, state, zip across a row in 6 > seperate cells/columns. > > I want to have this format in 1 cell: > > Name > PO Box > Street address > City, State Zip > You do realize that this will screw up your ability to sort the data. A better solution would be to describe what you want to do with the data. You may not need it in the ...

How do I format a cell to auto date
I would like to have a date automatically enter itself each time I open the spreadsheet. Is there a way to do this? Any help would be appreciated. Thanks Hi George Use a function like this one for todays date =TODAY() -- Regards Ron de Bruin http://www.rondebruin.nl "George" <George@discussions.microsoft.com> wrote in message news:7EF21130-EBD9-41C2-8CB5-5723BE40CBB1@microsoft.com... >I would like to have a date automatically enter itself each time I open the > spreadsheet. Is there a way to do this? Any help would be appreciated. > Thanks ...

CRM 3.0 Upgrade. Missing notes in Cases, that were there in 1.2
Just finished my upgrade from 1.2 to 3.0 on Monday. Have been upgrading SFO clients over the last couple of days. All data came over fine, and all looks and is working great. Had a couple of users report that notes they had put in some of their Active Cases in 1.2 are no longer there. If they click the drop down arrow, the notes are in the summary. If the case is opened they are nowhere to be found. This is true in both the web/SFO client. Anyone have any ideas? Cheers, Terry I know of a limitation in v3 whereby the note title does not display in the note list view, this can be more ...

Repeating columns cuts off merged cells
Hi all, I hope you can help because I can't find a solution to this one. I've got a two page spreadsheet (side by side) and I'm trying to repeat columns A & B because their row headings on the left and should only appear when printed (split onto two pages) (as in print setup > columns to repeat at left > $A:$B), ...... but B57:I57 are merged and wrapped (another grief that merged wrapped cells don't auto height, but that's for another day) and so are B58:I58 and B59:I59 (footer things). ...... and when printed, B57:I57, etc. cuts off on the second page and...

I'd like to have the X-axis a fixed length.
As it is, the length of the x-axis in x-axis units (read time) may of course be fixed, the the physical length on the printout (centimeters), changes to allow for more or less digits on the Y-axis. I would like to be able to fix the length of the X-axis so I can compare prints with a Y-scale of 0-10 with another with a Y-scale of 0.000 to 1000.000. Kind of a field for "always leave room for up to 9 digits on the Y-scale" (and if that must be exceeded, first reduce the number of decimals, then revert to scientific notation, and only _then_ shorten the X-axis.) Regards / Ake ...

Problem Upgrading To Microsoft CRM 3.0c
I am trying to upgrade from 3.0.5300.0 SBE to 3.0c on my laptop for the Outlook Client. I keep getting to the point where it tells me that I have to install the required Microsoft SQL Server Express Edition with the minimum version of 9.00.2407 and when I try to do that by clicking on the Install button it looks like it is trying to do that for a while then I get a message "Extraction Failed: Data Error (cyclic Redundancy Check) and I can go any further. I have tried this several times and I can only get to this point. I had installed v3.0c on this laptop using the same cd that I...

Digital signature problems
I have created a template with macros, but to run them my coworkers have to set their macro security settings to low. (I don't have this problem because for some reason my machine is "stuck" on low security and can't be changed). I work on a DOD computer and I can attach a DOD certified signature to the project, but apparently only other DOD computers accept that as a trusted source automatically. 1) Does anyone know if a non-DOD computer can somehow be told to accept the digital signature as a trusted source so it can work on their computer without having to reset the mac...

How to extract information from ACPI Hardware Ids for a device?
HI, Hardware ids are used to map a driver with a Hardware. I want to extract information from them. For Hardware ids created using PCI, IDE bus, information related to their format is available in msdn. Below listed are some Hardware id's of ACPI. How to extract information from them? Or what extctly they mean? How they are created? For Processor, 1. ACPI\GenuineIntel_-_x86_Family_15_Model_4 2. *GenuineIntel_-_x86_Family_15_Model_4 3. ACPI\GenuineIntel_-_x86_Family_15 4. *GenuineIntel_-_x86_Family_15 5. ACPI\GenuineIntel_-_x86 6. *GenuineIntel_-_x86 For Keyboard, 7. ACPI\PNP030...