Excel: Auto converting text to numbers

I am downloading an Excel sheet, and the numbers come in as text.  It
basically comes in as "33 %" but Excel registers this as text, not a
percentage.

I have a cell that will be used to add the numbers, but since they are
text it doesn't work.

Given this information, is there a way to convert the imported data
into numbers.  I would prefer to include this into my formula. The
potential numbers are:

0, 1, 2, 3, 4, 0 %, 25 %, 33 %, 50 %, 67 %, 75 %, 100 %, and N/A

I would prefer a function, again if possible, that could convert any
number.  Please note, the space between the number and the % sign is
there on purpose.

0
10/19/2005 5:52:19 PM
excel 39879 articles. 2 followers. Follow

1 Replies
2846 Views

Similar Articles

[PageSpeed] 27

You could try this:

Select an empty cell
Edit|copy

Select the range of "numbers" to be fixed
Edit|paste special|values
check Add

When I did this, my "75 %" cell changed to .75.  That's still the same value,
but it lost its format.

If you're getting this data from a web page, you may have non-breaking spaces
(HTML stuff) in those cells.

You may want to try David McRitchie's routine to clean that stuff up:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Avi wrote:
> 
> I am downloading an Excel sheet, and the numbers come in as text.  It
> basically comes in as "33 %" but Excel registers this as text, not a
> percentage.
> 
> I have a cell that will be used to add the numbers, but since they are
> text it doesn't work.
> 
> Given this information, is there a way to convert the imported data
> into numbers.  I would prefer to include this into my formula. The
> potential numbers are:
> 
> 0, 1, 2, 3, 4, 0 %, 25 %, 33 %, 50 %, 67 %, 75 %, 100 %, and N/A
> 
> I would prefer a function, again if possible, that could convert any
> number.  Please note, the space between the number and the % sign is
> there on purpose.

-- 

Dave Peterson
0
petersod (12004)
10/19/2005 8:05:11 PM
Reply:

Similar Artilces:

Can excel formulas become frozen?
Can formulas become frozen? I have a worksheet containing a basic percentage formula (100-(100/B1)*C1) This formula was working perfectly and producing the expected result when it was originally entered. I then saved and closed the worksheet When the worksheet was reopened and the figures in columns B and C wer changed, the previous result did not alter. If you retype the formula i each individual row it works ok again, but no amount of copying, pastin makes the original formula work. This applies to two other formulas on the same worksheet. It is almos as if they have become disconnected fr...

Incorrect Number Format
Hi: I try to set a cell format "Number" with negative number display in a (1234.00) layout. Hoever, in Format Cell, it shows negative number layout as -1234.00(black) -1234.00(red) -1234.00(black) -1234.00(red) (1234.00) is replaced. I try to modify the format in 'Custom' so that it layout looks like (1234.00). But my Excel does not recognize it as a number format and the negative number layout is shown as same as above. What can I do??? Thanks John I am not sure what you mean, is this the format you are looking for? 0.00_);(0.00) -- Regards, Peo Sjo...

Formatinc cents in Excel
This is a multi-part message in MIME format. ------=_NextPart_000_0023_01C64375.83FC6A30 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Hi guys, I hope you can help me with this one. In a cell I may have a value like 0.05 or 0.16 or 1.16 (these are dollar amounts) I will like to format these numbers following this criterion: If its by instance 0.05 ---> 5 cents If its 0.16 ---> 16 cents If its 1.14 ---> I willl leave 1.14 Thanks ------=_NextPart_000_0023_01C64375.83FC6A30 Content-Type: text/html; charset=...

EXCEL export to SQL query
Hi All, Hi guys - I'm a bit of a newbie to this programming lark and am struggling - I am trying to update a SQL database from a form in excel, below is the code that exists however doesn't seem to work, actual feilds on Spreadsheet and SQL DB are as follows Department;UserName;TelLogin;LoginID;UserID;TL Private Sub CommandButton2_Click() 'Inserts a new record into an SQL Table Dim con As Object Dim d As String Dim boolcheck As Boolean Set con = CreateObject("ADODB.Connection") Sheets("Sheet2").Select Selection.Range("...

A converter for Publisher 2003,2007 files to open in Publisher 20
If I am sent Publisher files made in the newer versions of Publisher, why wont they open in Publisher 2000? Is there a converter available as there is for Word file conversions? This incompatiblity between versions is a major irritation. Why does Microsoft FORCE people into costly upgrades when previous versions are working well?? Later versions of MS programs are sometimes worse containing more annoying features than the more straightforward previous ones. The change of layout etc is also disconcerting. On Wed, 7 May 2008 16:30:01 -0700, Mikerawl <Mikerawl@discussions.microsoft.co...

How do you change the right margin of a header in Excel?
Is there any way to change the right margin in Excel, or is it going to default no matter what? The margins of left and right headers cannot be changed. The only way to move them is to pad with spaces. i.e. enter your right header as custom then add several spaces to move it left. You cannot move it more to the right. Gord Dibben Excel MVP On Wed, 19 Jan 2005 14:19:05 -0800, ncford <ncford@discussions.microsoft.com> wrote: >Is there any way to change the right margin in Excel, or is it going to >default no matter what? Thanks for your help. However, I can't get it...

Filter Form based on OnClick from Text Box of another form
Hi, I have a job list form with basic info on it. I want to be able to click on the job number text box which then opens up a more detailed form and applies a filter so that I only get the record for the job number that I have clicked on from the underlaying form. Could someone please tell me how to do this as I am guessing it should be fairly simple although I can't figure it out. Thanks in advance Dim strWhere As String strWhere = "[JobNumber] = " & Me.[JobNumber] Debug.Print strWhere 'for testing only DoCmd.OpenForm "NameOfForm", , , str...

Excel screen doesn't update correctly
Hi Something strange is happening, when I go from one cell to another the screen appears to update, but gets mixed up. The only way I have found to fix it is to do a print preview or select another tab but now it is at the point it happens every time I put data into a cell. This is very frustrating, does anyone have any idea why this is happening? Thanks in advance Tanya Hi, check if you have your calculation method as manual, change it to automatic "Tanya" wrote: > Hi > Something strange is happening, when I go from one cell to another the > screen ...

Floating text
Hi everyone, I'd like to have some question+answer showing up on top of all my applications() to make me read it and memorize it. I know that I can hide Excel, but how can I hide the form but the text on the form to be visible? There is anyway to do it in VBA? Can you please explain further? This doesn't really make any sense to me. Please give a specific example. Before/after examples usually work great! -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Dan Tabla" wrote: > Hi everyone, &g...

How can I clear the last Data->Text to columns to formatting
I've noticed in Excel 2000 that if I paste text into various worksheets within a workbook each paste will assume the Text->Column formatting that I applied in the previous. How can I prevent it from happening ? Thanks Steve Just run another data|Text to columns against a dummy cell. Specify delimited, but remove all the check marks from all the possible delimiters. (alternatively, you can close excel and reopen it.) svaardt wrote: > > I've noticed in Excel 2000 that if I paste text into various worksheets > within a workbook each paste will assume the Text->Col...

sequential numbers
I simply want to sequentially number rows in a column. However, some of the rows do not contain any data so do not need to be numbered. So there is in fact a blank. It´s also possible that i´d want to add another row, and auto update the number sequence. You cannot drag the auto numbering facility across cells that you want to remain empty of course, because they will be numbered. With your data in column A,: Formula in B1 :1 In B2: =IF(A2="",B1,B1+1) Fill down. Format B2: Format>Conditional Formatting Cell Value is, Equal to, B1 (remove the $signs if you clic...

Access VBA creating an Excel spreadsheet with a total line
I have an Access module which dynamically creates an Excel spreadsheet. Now the Business User has asked that we put a Total Line in the Excel spreadsheet. How do I within Access VBA determine the range, because the spreadsheet will never be the same, and then use that range to create a formula to insert to the local recordset which dynamically creates the Excel spreadsheet with the Total Line??? Thanks in advance for your review and help and hopeful for a reply. PSULionRP On Dec 22, 10:35 am, PSULionRP <PSULio...@discussions.microsoft.com> wrote: > I have an Acces...

Microsoft Support Telephone Number?
Is anyone able to provide a telephone number for Microsoft support? Attempting to install an updated Nvidia 790i motherboard driver resulted in a system that recognizes input from neither the PS2 keyboard nor the PS2 mouse, and after the initial Windows XP loading screen with the progress bar at the bottom, the screen is black. Apparently XP is running from the indications of the hard drive select light, and the fact that it seems to go into sleep mode if left unattended. But plugging in a USB mouse doesn't seem to do anything useful. Booting into the Recovery Console from ...

Automatically have even numbered rows shaded gray?
I have a big long spreadsheet (great description!). Right now i'm using the AutoFormat option to make every other row (e.g. rows 2, 4, 6, 8, etc...) have a color shade of light gray. The complication of this is: 1) Due to length and amount of data in spreadsheet, takes a LONG time to make the change 2) Have to repeatedly re-AutoFormat as I add more information to the spreadsheet 3) Because of some special titling at the top of the sheet, I have to manually select a few rows down from the top, then select the bottom-most row before AutoFormatting I'm wondering if somebody could...

Converting date to day number
I want to have two columns: the first with date; the second with the corresponding day number. How do I do that? I don't care what the day numbers are - as long as they are sequential. -- Chris Excel already stores dates as sequential numbers. Assuming your date is in A1, in B1 type =A1 and format as General. -- Ian -- "Chris" <nospam@[127.0.0.1]> wrote in message news:k38EevWZrclDFw4v@[127.0.0.1]... >I want to have two columns: > the first with date; > the second with the corresponding day number. > > How do I do that? > > I don't care what...

watermark in excel.
can i enter a watermark as a background in excel?and if yes is this watermark appear in printing?? Go to File/PageSetup. Go to the header/Footer tab. Click Custom Header, click in the Centre Section area, click the Picture tool (2nd from right) and insert the text or image that you want as a watermark. Click the Format Picture tool (1st on right), click the Picture tab, set Image Control to "Washout". That should do it. Rgds, Scott "Ahmad Al-Nahar" <AhmadAlNahar@discussions.microsoft.com> wrote in message news:E4DAEF9E-4DA1-4494-8E26-6DAEB01F05F2@microsoft.com....

Stripping Blank Lines from text box
Hi I have a text box on a form called 'disAddress' with 'Enter Key Behavior' set to 'New Line in Field' I have 9 variables D(0) to D(8) that contains lines of addresses for exmple D(0) could equal "123 Freet Street" D(1) could equal "Near Dartford" and D(3) could equal "" and D(4) could equal "Australia" etc. I am tryng to display the address in a text box with no blank lines taking the information from the variables. D(0) = "25 Albert Road" D (1) = "" D(2) ="Wilmington" I dont want it to show ...

Report Character Limitations in text block
I am trying to type a contract in 1 text block. I can get about 3/4 of a page, and I can type no more. I have to create a second text box to continue. Hope someone can help. Thanks, -- Bill If you are using a memo field then I suggest using a Word or Wordpad document with hyperlink field. -- Build a little, test a little. "William Lawrence" wrote: > I am trying to type a contract in 1 text block. I can get about 3/4 of a > page, and I can type no more. I have to create a second text box to continue. > > Hope someone can help. > >...

Auto-Hide and Always On Top Plug In or Code?
I have developed a small app I would like to have behave like the Windows XP Taskbar, in that users should be able to select either Auto- Hide or Always On Top, and get the same behavior you get with the Taskbar. Is this something I should be able to accomplish in code? When I first started this project months ago, I didn't think so, so I convinced my firm to purchase LogicNP's Shell Mega Pack Sorry, too fast the fingers. Anyway, there are a number of issues with the ShellAppBar tool that make it undependable, and LogicNP seems to no longer be supporting the product (or...

GP Reports NOT displaying full Account Number
My GP financial trial balances for some reason does not display the full account number. My account structure is XXXX-YYYY, but reports only show XXXX-YY, in other words 2nd segment the first two account numbers don't display on reports. I'm on GP 10 and had this same problem pre-upgrade. I have tried running checklinks on Reports. I have tried expanding the display width in Account Format setup but still no luck. I switched from the modified report to the default un-modified GP report, but still the full account number is not displayed. Does anyone know how to fix this proble...

Publisher text disappears on saving
I have a 25 page doc with both text , tables and photos. When saved and closed,on reopening the text has gone, photos and tables are OK as before saving. Can anyone help, this is the second time I've had to retype Is this a recent occurance? Have you made any changes to your system lately? Have you tried moving various parts of your publication to see if the text boxes are hiding behind something else? Like maybe a line that goes around the page? -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "Ronin" <Roni...

Word.doc's opened in excel
I have a spreadsheet that contains index information. I need to be able to click ( ? double click) on a cell that contains word.doc description (eg WI000_24.doc) and have that document open i word. There are 265 doc's listed in a column. There are some blank cells in the column. The column contents will change (daily / weekly) Your help is appreciated. Chri -- Message posted from http://www.ExcelForum.com Try this Add a button on your worksheet and assign this macro to it It will open the file that is in the activecell (change the path in the macro to yours) Sub test() ...

Number formats
HYCH Am using the code below, to pull back the a list of Times (format hh:mm) But still shows as decimal numbers any help please !!! Private Sub UserForm_Initialize() Dim cPart As Range Dim ws As Worksheet Set ws = Worksheets("Lists") For Each cPart In ws.Range("C1:C88") With Me.ComboBox1 .AddItem cPart.Value .List(.ListCount - 1, 1) = Format(cPart.Offset(0, 1).Value, "hh:mm") End With Next cPart Me.ComboBox1.Value = "" Me.ComboBox1.SetFocus End Sub Steve Hmm... With times in D1:D88, times show, as times, for me in the seco...

Linking data from .CSV to Excel
Hello! I'm completely stuck. I have an excel spreadsheet that uses data fro a .CSV file. This data (all numbers) is linked to a sheet in th workbook. The problem is this: in order for the data to be updated, it is necessary to open up th .csv file whilst the workbook is open. This is a terrible hassle. have tried making a macro that opens the .csv file and then closes i again, but that doesn't appear to update the linked data fields. Th Edit/Links/...update links function won't work, since Excel say "External Copy: file format is not valid". Does anyone have any...

File format converter won't work
This is a multi-part message in MIME format. ------=_NextPart_000_000E_01C5F418.4A470D10 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable After several months without problem, Publisher 2003 now will not open a = text file created in Word. I get this error message after I try to = insert it into a text box: "Publisher cannot start the file format converter C:\program = files\Microsoft shared\Textconv\msconv97.dll" I KNOW msconv97.dll is there. I've run the "Fix" program and also = uninstalled Publisher and then r...