Cropping text from a number

Hi 
I am doing a staight comparisions of two numbers in two 
different cells (a percentage increase)

However one of the numbers I am using has an asterisk on 
the left hand end of the number.

Obviously excel cannot calculate the increase given it is 
not redaing the cell as a number.

How can I right a formula so that excell does not read the 
asterisk rather only the number


any help would be appreciated
0
MLOMAX (1)
1/9/2004 5:55:02 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
612 Views

Similar Articles

[PageSpeed] 54

Hi Matt!

You could set up an extra column to the right of the offending numbers
and use:

Data > Text to Columns

Or you could set up and extra column and parse the offending numbers
with the formula:

=--RIGHT(A1,LEN(A1)-1)
Or:
=VALUE(RIGHT(A1,LEN(A1)-1))

The formulas could be used in a formula:
e.g.
=A2/--RIGHT(A1,LEN(A1)-1)

-- 
Regards
Norman Harker MVP (Excel)
Sydney, Australia
njharker@optusnet.com.au
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.


0
njharker (1646)
1/9/2004 6:14:21 AM
Hi Matt,

Just another alternative that doesn't require helper 
columns:

A1 = *82
B1 =  85

=--SUBSTITUTE(A1,"*","")/B1

Biff

>-----Original Message-----
>Hi 
>I am doing a staight comparisions of two numbers in two 
>different cells (a percentage increase)
>
>However one of the numbers I am using has an asterisk on 
>the left hand end of the number.
>
>Obviously excel cannot calculate the increase given it is 
>not redaing the cell as a number.
>
>How can I right a formula so that excell does not read 
the 
>asterisk rather only the number
>
>
>any help would be appreciated
>.
>
0
biffinpitt (3171)
1/9/2004 6:40:04 AM
Reply:

Similar Artilces:

Copy here as Note with Text
I have an all day appointment which I believe Outlook call an Event. In this event I have text in the bottom box. I now try to copy this event to the Notes folder. The message " copy here as note with text" pops up and I go. When I open the note there is no text from the original event. This used to work fine in 2000, but no longer seems to work. Is this a problem or what? It is for me. Thanks In Advance Ralph Walti The Buyers Alternative www.TBAgifts.com ...

Forcing Text Import Wizard to run
Is there any way that I can force the Text Import Wizard to run when opening a file, from the command line, in Excel? We have a program that outputs .rpt files, which are comma delimited, and I have added a file association to open them in Excel. Unfortunately they then open with all data in one column. I know I can change the extension to .txt, but then each file has to be opened from within outlook. I want to be able to double click the file and open excel automatically, with the Import Text Wizard started. I want to modify the file association configuration to force the text wizard to run. ...

How to set a cell to expand to fit text in Excel not shrink fit
I'm not sure it this is doable but is it possible to set a cell so that it automatically displays all the text that is in there, expanding where necessary without manual changes. i don't want shrink to fit as I need to text to be of a certain size Wrapping text can be one way or install such an event sub: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 1 Then Columns(Target.Column).AutoFit End Sub This works on column A! Post if you need help to install it! -- Regards! Stefi „Elisal” ezt írta: > I'm not sure it ...

excel text not displaying
I have seen this other places and now I see it here so I now know it is more than one computer problem. When I scroll down in an excel file some of the values are not there, then after scrolling back and forth they may show back up. I have tried different video drivers... Is this a well known problem I have just not seen the answer to? Any suggestions? Hi, This could be a screen refresh issue, it could be driver related, it could be hardware related. there are many listings in the Knowledge Base regarding screen refresh but none tied specifically to Excel. -- If this helps, p...

Entering Data Into Text Boxes and Worksheet Protection
I'm wondering if there's a way to enter data into a text box once a worksheet has been protected. Currently, once the sheet is protected, there's no way to enter data into a text box. Any thoughts appreciated. Thanks. Stephan There are two different textboxes--one from the drawing toolbar and one from the control toolbox toolbar. But you can rightclick on each and choose: format control (or Format CheckBox There's a tab call protection. Each has a Lock option. (the drawing toolbar has Locked and Lock Text) After I unchecked each of them, I could use them (when I protect...

CEdit will not accept text. What do I do?
Here is the scenario. I have a dialog which owns a popup window which owns a cedit control. When I set the focus to the edit control the caret blinks in the control and I can use the following keys: Insert, delete, home, end, page up, page down, and the arrow keys. I can't type any character into the control. I can highlight text and delete it, I can right click and paste into the control, but when I hit a letter, number or symbol key nothing happens. I have placed a break point in the control's OnKeyUp and OnKeyDown functions and the points are hit. I have even tried calling OnChar fr...

person number
How can I in excel calculate the age if I have the person number YYMMDD-xxxx? 500101-1234 --> 56 =DATEDIF(DATE(LEFT(A1,2),MID(A1,3,2),MID(A1,5,2)),TODAY(),"y") -- Kind regards, Niek Otten <f_ringberg@hotmail.com> wrote in message news:1140518042.263239.177070@g47g2000cwa.googlegroups.com... > How can I in excel calculate the age if I have the person number > YYMMDD-xxxx? > > 500101-1234 --> 56 > Datedif? I have excel swedish version, i don't find it... Niek Otten wrote: > =DATEDIF(DATE(LEFT(A1,2),MID(A1,3,2),MID(A1,5,2)),TODAY(),"...

Converting a publisher file to adobe, with editable text
I prepare publisher files, such as postcards, to send to people in my group. I want them to be able to insert their information when applicable. Is there a way to do that in Publisher, or will I neet a different program? Acrobat Pro can create fill-in documents. Word can too. Not so Publisher. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Linda" <Linda@discussions.microsoft.com> wrote in message news:EE418097-D3AA-4B25-80E7-4B625EAF54D9@microsoft.com... >I prepare publisher files, such as postcards, to send ...

Text in auto text building block changes
I am new to 2007 and am trying to set up a template for our authors' use. I'm using an existing 2003 template. I created an autotext building block -- a table with our styles applied and the name of the style. Added it to a custom gallery and put that on the QAT. When I inserted the table, Chart Text style had changed to 10 point Times New Roman and Chart Number was 12 point bold Arial in all caps. When I went to the Style window, the style was correctly defined. I'm using an older template that I have been a bit suspicious of -- numbering issues. Could it be co...

How to set CListCtrl item text color?
I use MFC Visual C++ 4.2. I need to set certain item text color to RED. I need to do it per row. So one or more row text color will be RED( depend on the item state, I decide it myself ) and the others are DEFAULT (BLACK ). How to do this in a simple manner? I mean if it possible without having to use CustomDraw or somekind like that. Thank you. On 1 oct, 10:23, Landon <Lan...@discussions.microsoft.com> wrote: > I use MFC Visual =A0C++ 4.2. > > I need to set certain item text color to RED. I need to do it per row. So > one or more row text color will be RED( depe...

A query to output custom text?
Hi all, is there any way I can setup a query to output custom text? eg. col. 1 output field name col. 2 output field age col. 3 output "New Student" "New Student" is the custom text I would like to generate everytime. I know I can make a field with "New Student" filled for all records, but it looks redundancy to me. Thanks in advance! Hey Nature, not sure exactly if this is what you want. But if you go to the text box that you want "New Student" to show up... just go to the properties of the text box and under control source type ="New St...

Converting to text file
Do anyone know how I can convert an excel file to a flat file without opening up excel. Please help. ...

Removing text #2
I have a column of names with text and numbers at the end eg J Smith ABC 5.0 I'd like to remove the last characters leaving the names only. Can anyone help? Thanks DR Hi maybe a formula like =TRIM(LEFT(A1,LEN(A1)-3)) in an adjacent column -- Regards Frank Kabel Frankfurt, Germany DREED wrote: > I have a column of names with text and numbers at the end > > eg > > J Smith ABC 5.0 > > I'd like to remove the last characters leaving the names only. > > Can anyone help? > > Thanks > > DR One way: =LEFT(A1,MIN(IF(ISNUMBER(1*MID(A1,ROW(...

Text Not Showing in Cell
I'm using excel to produce reports with almost all text. Some cells have a lot of words. Text Wrap is on. Row Height is set to AutoFit. Most cells show up just fine. Some have hidden words that run on under the next cell below. I have even encountered some cells that cut off right through the middle of the text. I can, of course, manually change the height of each row, but this report is over 200 lines long. Same problem happens when printed. Please help. Excel is not the greatest text editor. Have you considered doing the report in Word, using the Tables function? You co...

How to tab between text-boxes?
When I enter text into text-boxes, I want to be able to jump from one textbox to another simply by using the tab button Is this possible? Is it also possible to decide in which order to jump? The textboxes are created using the design view and they have no code attached Thank Textboxes on a worksheet ? You have to program it for each and every box. Rightclick the box, "view code", paste this in: Private Sub TextBox1_KeyDown(ByVal KeyCode _ As MSForms.ReturnInteger, ByVal Shift As Integer) If KeyCode = 9 Then TextBox2.Activate End Sub (This will err in Excel97 due to a minor ...

Excel default formatting of numbers
I have a CSV file which is generated by an inhouse application. One of the columns in this file has a four digit code, which are text but made of numerical digits. Some of the codes start with one or more zeroes, ie 0012, 0013, 0014 etc. The problem is when my users open the file in Excel the column has been formatted as numbers and the leading zeroes have been trimmed, so 0013 becomes 13. I do not want my users to have to play around with formatting. Also even if I do select the column and change the formatting to text the leading zeroes are still missing. Please could anyone help me with a ...

crop
I am trying to crop a person out of a family picture A small child turns to Ed, and exclaims: "Look! Look! A post from Gibbs <anonymous@discussions.microsoft.com>!"... > I am trying to crop a person out of a family picture Good for you! -- Ed Bennett - MVP Microsoft Publisher http://www.mvps.org/the_nerd/ Before reading this message, view the disclaimer: http://mvps.org/the_nerd/disclaim.htm Did you have a question? -- Ed Bennett - MVP Microsoft Publisher http://www.mvps.org/the_nerd/ Before reading this message, view the disclaimer: http://mvps....

Outllok Rule Creation based on Greater Than Numbers...
Program: Outlook 2003 He Everyone, My boss has requested the creation of a particular type of rule that has me stumped. He receives many quote requests per day but we are only interested in them if they are BELOW a certain quantity, i.e. we would be interested in fulfilling an order for less than 1000 but not more than that. I have tried filtering based on "Specific words in the body" with entries such as ">1000," (hoping Outlook would know to treat the entry as a number above 1000, also tried "1,***," (hoping it would recognize as a number w/wildcards)...

Convert all form fields to text.
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel How to I convert a form field in a document into the text that has been entered into the field? I want to be able to convert a document that contains fields into a document that looks the same but that does not contain any fields. I could do this in Word 2003, but do not know how to do it in Word 2008. Command+A to Select All, then Shift+Command+F9 to Unlink the fields. HTH |:>) Bob Jones [MVP] Office:Mac On 4/22/10 11:10 PM, in article 59bb7545.-1@webcrossing.JaKIaxP2ac0, "Mike_Moroney@of...

Reading text from text field.
Hi and Hello Everybody, A very Good Morning to all the members. Please help me in solving my query.... The requirement is to take just the contents/data (i.e. values) a plain text. Store the two columns information as text.. suppose, in excel file. We have some "Topic names" and corresponding "values", shown in text-fields coming in browser window. If we give 'Select all' option under 'Edit' menu-option in browser (Internet Explorer) window, the contents ofcourse get copied. Now If we, suppose, open blank sheet in Excel or a blank document i word, an...

Bottom alignment with baseline in text box
Hi, I have a one-line text box and I'd like the baseline of the text to align with the bottom border. I set the vertical alignment of the text box to "Bottom" and the internal bottom margin to 0, but, of course the descent space still appears between the baseline and the box border, so that the text does not exactly "sits" on the border. As the text is in capital in sans serif font, I don't have any descent in my glyphs, so is there a way to force alignment with the baseline, or do I have to fine-tune manually (not fitting my text box to my guides)? Thanks! Raph...

Want to see only specific number of records on a form
Hi I have a form with default view as "Continuous Forms". When I openthis form I only want to see the records equal to Recordcount i.e. ifI have 3 records I should only see three records. I dont want to seean extra one record which is blank. The easy way is simply to set the allow additions to "no".(you will still be able to add records via a code, or perhaps a "add" button you provide.Or, perhaps you don't even need add record ability...Anyway...give the above a try..it will remove that extra "new record" entry....-- Albert D. Kallal (Access MVP)...

Info text box
Hello, I'm using Excel to do my budget and I'm trying to figure out a way to create a macro the, when launched, will give me a box in which I will enter some details: Field 1- Date (MM/DD/YYYY) Field 2- Description Field 3- In (enter an amount of money in section 3, 4, 5 or 6) Field 4- Out [Paiements] (enter an amount of money in section 3, 4, 5 or 6) * Field 5- Out [ATM] (enter an amount of money in section 3, 4, 5 or 6) * Field 6- Others (enter an amount of money in section 3, 4, 5 or 6) * * By default, I would need to see "0.00" Once I click on DONE, it would take th...

How do I crop a chart imported from another worksheet?
I need to reduce the excess space around the graph, but without affecting the text or bars in the graph. If you don't need to keep it behaving as a chart, you can copy it as a picture, paste it, and then actually crop it. If you copy it using the Picture option, you can double click on the picture and ungroup the separate drawing objects, then shrink or delete the rectangles that comprised the chart area. If you copy as a bitmap, you can use the crop button on the picture toolbar to crop it. If you want to keep your chart as a chart, it's not so easy. First you need to format a...

can I sort text data into bins?
Hi, Here's my problem - I hope you can understand it & help: Let's say I've got a column, A (or row 1, it doesn't matter) tha contains cells, each with a single word/category that I'm interested i [example: dog, cat, bird, fish, gerbil] - we can assume that this is m list of category headers/titles. Plus I've also got various other columns, A-D (or rows) next to m column A titles that each contain a list of items, one per cell: [eg B1=dog + B2=cat, C1=bird + C2=cat + C3=gerbil]. Perhaps it would be clearer if I drew it out (ignore the dashes they're just t...