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
951 Views

Similar Articles

[PageSpeed] 52

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 (3172)
1/9/2004 6:40:04 AM
Reply:

Similar Artilces:

How do I set a macro to print variable number of pages in excel?
I have a worksheet that calculates total nuber of packages of a consignment; I am now trying to run a macro that would print as many labels as the total number of packages, which could be as little as one or as much as 50. I tried to copy and paste into the pages box of the print screen, but it didn't work. Does anybody have a solution? The excel version is Office 2000. Thanks Livio, Put the print instruction in a for-next loop. If the number of packages is in, say Cell T6 then: For PrintRun = 1 To Range("T6").Value Print a label Next PrintRun Henry "Livio" &...

Numbering oddly
Qt based on Word 2007 Is there an easy way to set numbering to the following: 1. 3. 5. And so on Use the following field construction { = { SEQ Odd } * 2 - 1 } You must use Ctrl+F9 to insert each pair of field delimiters and use Alt+F9 to toggle off their display. After creating the field construction, you could select it (and probably the following tab) and create an autotext entry of it to facilitate its use. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins ...

summary count of unique numbers
I am trying to create a supplier delivery performance spreadsheet and I need to be able to show a count of the total number of unique orders placed for each supplier. The worksheet is sub-totalled on a count of the number of receipts made but one order can have one to many receipts. SuppNo OrderNo ReceiptNo 123 9961 1 456 9978 16 456 9982 33 789 9999 46 789 9999 57 Therefore SuppNo 123 has 1 Order(s) and 1 Receipt(s) 456 ...

Adding a ' to the front of a column of numbers
Hello everyone, I have two columns of numbers in excess of 35,000 lines. I need to do look up from to the other. The problem I'm running into is that th first set of numbers has a ' in front it but the other one doesn't. Example: First column '123456 Second column 123456 I want to add the ' in front one column and I'm having some problems. Concatenating isn't seeming to work. Any ideas? Thank you, Eddi -- Message posted from http://www.ExcelForum.com Hi why not remove the ' from the other column. e.g. with a macro such as: sub remove_it()...

text display in MDI
Hello Friends Iam niranjan Developing MDI Application in vc++6.0 when iam displaying text in windows iam using OnDraw and OnChar functions. in OnChar function iam incrementing the x,y positions accoording to Key strokes. and making the string buffer to empty in "Enter" Key stroke in OnDraw function by using pDC->TextOut(x,y,str) iam displaying the text here iam not getting the proper data.... my problems are.... 1. after giving ther enter key curson going to next line but previous lines data in erasing , after ever enter key.. if any one knows solution plz help me to get th...

Constant number of lines per group
I need to print a constant number of lines per group, as my report needs to print on pre-printed forms, and the data needs to land in the correct location on the paper. I found the following support article on Microsoft's web site, but it indicates that it works only through version 97. Hoping it would still work anyway, I followed the instructions, but it did not work for me in Access 2007. http://support.microsoft.com/kb/q119073 Does anybody know how to accomplish this in 2007? TIA. "... did not work ... in Access 2007" does not tell us much. Did Access print anything?...

text in a vertical column
I am trying to get a word in a coulum without having to enter each letter with a return after. In article <FA7EDA2C-5B2C-4F96-A1E3-EA6EA2913A10@microsoft.com>, Jabiru wrote: > I am trying to get a word in a coulum without having to enter each letter > with a return after. ... in what program? ...

assign numeric value to letters and sum with other numbers
I apologize if I am duplicating an earlier question, but I can't find the answer. How do I sum a row or column that has numbers and letters by giving the letters a numerical equivalent? -- WJG On Mon, 11 Jan 2010 12:19:01 -0800, Galadad <Galadad@discussions.microsoft.com> wrote: >I apologize if I am duplicating an earlier question, but I can't find the >answer. How do I sum a row or column that has numbers and letters by giving >the letters a numerical equivalent? Could you give an example of input and expected output. Lars-�ke Just guessing...

Maximum number of characters in merged cells
Is there a maximum number of characters a merged cells will hold? I have a text field of merged cells however, after about 11 rows all of the text does not show even though it looks like there is room for the additional text. Bill, Do a search in help with "Specifications" and you will find: Length of cell contents (text): 32,767 characters. Only 1,024 display in a cell; all 32,767 display in the formula bar Bernard "Bill" <anonymous@discussions.microsoft.com> wrote in message news:A2366FCA-2E93-42E3-82A5-92316657DF4C@microsoft.com... > Is there a maximu...

Cannot select text with shift key
Hi! I have a pretty annoying problem with Outlook 2007 SP1. When I write some text and directly tries to select the text I've written with the shift and arrow key it will not select the text. I can easily reproduce the error by doing the following steps: 1. Open a new mail 2. Tab down to the text area 3. Write "hello" 4. Hit Shift+Left Arrow The expected result would be to select the "o" in the written text, but the actual result is that the letter is not selected. It is the same problem with Ctrl+Shift+Left or Shift+Home. I run it on Windows Vista x64 SP1. When I t...

sorting numbers #5
Hi were have problems in sorting out our inventory item numbers. We have over 15,000 item numbers that can't be changed or zeros deleted. The problem is when there are zeros in the item number. Excell sort CA0002 CA00020 CA0021 CA02 CA021 CA02C Needs to be CA0002 CA02 CA02C CA00020 CA0021 CA021 Thanks Darkjedi -- darkjedi ------------------------------------------------------------------------ darkjedi's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29153 View this thread: http://www.excelforum.com/showthread.php?threadid=488748 Since I didn't re...

Shift and number
Hi, This started happening while I was working in Excel 2000, I tried to use the equal sign "=" in a formula and instead of getting the equal sign I was getting Pts instead. And the ")" was giving me something different. This only happens in Excel. Anybody has had this problem? Thanks Hello No idea why this should happen in Excel. But you can take advantage of Excel's CODE and CHAR functions. If A1 contains "=" then Code(A1) returns 61. To enter an equals sign from the keyboard, Hols down the ALT key and using the number pad type "61" then...

Sorting "text" numbers
I am sorting a list of numbers (actually telephone extensions) which need to be formatted as text. When I sort them I get two separate lists starting over again. e.g. 1000,1001,1004,2003,3456,...,0456,1003,2455,... I've check for leading spaces, format differences etc. but this is not the problem. Chris, If the cells were formatted after entering the data, the currently entered values aren't automatically converted from numbers to strings. Do your sort, then select the first group of cells and run this macro: Sub MakeText() For Each myCell In Selection myCell.Value = &q...

Cells containing lots of text will not wrap?
Hi, I have a report to complete on an excel sheet. Some of the cells contain lots of text and no matter how I format the cell I can't get it to show me all of the text. It behaves fine up to a certain point, line wrapping as it should but then it reaches a point where the text just disappears from view. It's still there in the cell but just not visible. Manually increasing the size of the cell has no effect. This is driving me nuts! -- Regards Jon If you add alt-enters to force new lines within the cell (every 80-100 characters), then you'll be able to see more characters...

Create Auto Text in Excel?
I want to be able to add the following function (=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename ",A1),1))) into any excel sheet that I'm working on like you can do in Word with Auto Text insert. I think I should be able to do this with a macro but I can't figure out how to do it. Any help much appreciated. Cheers Lee First, you can shorten the formula a bit and reduce the number of function calls by using =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,32) XL doesn&...

Page Numbering in Publisher
In putting together a large booklet with numerous graphics in Publisher 2002, I have had to split the document into 3 separate files. The page numbering in the first file works fine, but for files #2 & 3, the page numbering begins with "1". Is there a way to change this so I may begin page numbers at "30", for instance and have the pages number consecutively (i.e. 31, 32, 33, etc.)? In Publisher 2000 it's done this way. Tools Options General tab Enter the page number you wish to start with in the box. -- Don Vancouver, USA "Sarah Wright" &...

Saving as a csv file with text qualifiers
I have the following three values in 3 cells a1 b1 c1 adam john lilly I want to export this into a csv file that should look like this: "adam","john","lilly" How do I do it? Thanks in advance for the help One way: http://www.mcgimpsey.com/excel/textfiles.html#csvwithquotes In article <33b0685e.0501180614.1a04ff40@posting.google.com>, rs.pai@sbi.co.in (Rajendra Pai) wrote: > I have the following three values in 3 cells > > a1 b1 c1 > adam john lilly > > I want to export this into a csv file that should look like th...

Individual formatting on a text box on a report w/tabular layou?
Hyall, I can change the background color of the detail in a report with tabular layout (that is, similar to a continuous form) using Detail_Format: Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) With Me Select Case .txtCondition Case "Green" .Section("Detail").BackColor = vbWhite Case "Yellow" .Section("Detail").BackColor = vbYellow Case "Red" .Section("Detail").BackColor = vbRed End Select End With End Su...

why is an apostrophe in front of text
After entering a text in a cell or in the function field, when i go back in the cell an apostrophe apear in front of the text. How can i correct this? Thank you Try: Tools|Options|transition tab Uncheck "transition navigation keys" I've unchecked all those Lotus 123 transition settings. If you need them, don't do it. But if you're like most excel users, I'd uncheck them. mlu wrote: > > After entering a text in a cell or in the function field, when i go back in > the cell an apostrophe apear in front of the text. > > How can i correct this? &g...

How do I make the smallest number in a column red ( No VBA Please )
for a example in column E at cell E10 to E 30 I want the smallest number I enter in each of those cells to be red. If 3 cells are "0" all 3 should be red if one cell is "2" and all other cells are more than 2 I want that "2" cell to show up as red font. To sum it up in cells E10 to E30 I want the lowest numbers to be red if there are more than one of the lowest all should be red Thanks Ron On Sun, 5 Dec 2010 13:47:01 -0800 (PST), e_a_g_l_e_p_i <eaglepi@hotmail.com> wrote: >for a example in column E at cell E10 to E 30 I want the smallest >number I...

VBA : get number of rows in a spreadsheet
Is there any way in VBA that I can find the number of rows used on a spreadsheet (the active worksheet)? The reason I need this is because I am trying to add some extra lines to a sheet (and move the following rows down the sheet). Cheers Bill Dallas Bill, Dim myRow As Long myRow = Range("A1").SpecialCells(xlCellTypeLastCell).Row The above will often report more rows than those actually used, so often it is better to check a specific column for the last entry: myRow = Range("A65536").End(xlUp).Row HTH, Bernie MS Excel MVP "Bill Dallas" <bill.dallas@gb....

imbed excel cell text in a shape
I need to insert a number into a shape that corresponds to a cell in excel. Kinda like the exploded parts view in a car repair manual. A callout shape with a text value "123" that is linked to cell A1 in file ***.xls. It would be nice to be able to auto generate the callouts from the excel file. shape 123 is cell A1, shape 124 is cell A2 and so on. Thanks what version of visio are you running? al "mystified" <mystified@discussions.microsoft.com> wrote in message news:80B4E390-190A-41D9-AC4A-F96C1DA6FB6E@microsoft.com... >I need to insert a number into a s...

Text box in master document moves out of print range
I have placed a text box at the bottom of the master page using publisher 2003. I have set it to be compatible with all my printers. However, when I set the print option to greyscale it shifts the box to a position lower than any printer can handle. It often looks ok in preview, but not on the hard copy itself. I have also noticed that the text moves in some cases witht he help of additional tabs. Why is this. My document is 200 pages long with 15 chapters. I have to change each chapter 4 times (left & right pages masters A & B) to overcome this problem. Your help would be much ...

Manager Error 'Bad File name or Number'
I keep getting this message on one of my machines when I try to open manager. Admin and POS both open and operate fine. I have uninstalled and reinstalled twice to no avail. Any help would be much appreciated. Thanks. Open Administrator and go to File/Configuration. Check the Paths and Templates Tabs - you probably have an invalid path or filename in one of those fields. If you are using files shared on the network, you may not have permission to access the files or folders. -- Glenn Adams Tiber Creek Consulting http://www.tibercreek.com glenn@tibercreek.com ----------------------...

formating cell for indian number system
Can any one tell how can i format cell show that number can dispaly in indian number system. It means 12345 must display as 12,345. 123456 must be displayed as 1,23,456 and 1234567 must be displayed as 12,34,567 and so on ...