Convert Numbers, Seen As Text, To True Numbers
The back story is that from a system we use, when you download data from inside it, Excel treats most of the cells with a "Number Stored As Text" error. I had problems with this and it was just generally annoying so I wrote the below macro that converts each of the cells back to general cells and reinputs the data, essentially reseting the cell and keeping the data. Being that this is a loop, it takes a good while to run if alot of data was downloaded. Any idea's on how to speed it up besides screen updating? Note, other stuff happens in the first macro, but it is just cosmetic nonintensive things. It calls the second macro because I wanted them seperate should I want to just run the second.
Sub CleanData()
Application. ScreenUpdating = False
' Find the Right most cell and the Bottom most cell and
' run the macro from A1 to this cell recursively
Range("IV1").End(xlToLeft).Select
Col = Selection.Column
Range("A65536").End(xlUp).Select
Row = Selection.Row
Range("A2").Select
Range("A1", Cells(Row, Col)).Select
Application.Run ("FixTextAsNumberError")
Application.ScreenUpdating = True
'
End Sub..............
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Convert Numbers As Text To True Numbers
I have the following two macros that I use daily: Public Sub add_tick_marks() Dim c As Range For Each c In Selection If Not IsError(c.Value) Then If Left(c.Value, 1) <> "'" And c.Value <> "" Then If InStr(c.Value, "E+") > 0 Then c.Value = "'" & c.Formula Else I use these because I frequently copy billing account numbers that have leading zeros from some proprietary software and work with them in Excel. I need the leading zeros to facilitate VLookUp's. When I paste the data into Excel it truncates the leading zeros. My typical procedure is: 1. Copy a column of values from our proprietary software 2. Paste it in Excel (Ctrl+V) 3. While newly pasted data is still selected I hit Ctrl+1 which brings up the Format Cells form. 4. I double click "Text" 5. I repaste into Excel (Ctrl+V) 6. I run my macro to add tic marks to the values so the leading zeros never get lost. The problem occurs when I copy multiple columns of data and paste it in Excel. If one of the columns of data contains numbers or currency then I select that range of values and run the macro to get rid of the tic marks. After running the macro the numbers are still treated as text. When I select a range of those values and rightclick on the status bar and select " Sum" there is no value. No big deal, I just highlight them and format the cells as numbers or currency. But after formatting them as numbers or currency, they retain their format as text until I click in the formula bar for each individual cell and hit enter, at which time they take on the number or currency format I have chosen and the sum can then be displayed in the status bar. How can I force the formatting I chose to take effect on the data immediately without me having to individually "fake change" the data in each cell?
View Replies!
View Related
Convert Numbers Seen As Text To True Numbers
I have text as numbers downloaded to excel sheets from crystal that i need to add together on a summary sheet. I've tried using Value, TRIM and CLEAN functions to no avail. the TYPE function is reading the cell as Text and I have also tried multiplying by 1. Does anybody know how I can get around this ? hopefully I've attached an extract of the file
View Replies!
View Related
Convert Imported Values, Being Seen As Text, To True Numbers
I have a document passed to me that includes information pulled from an internal system. I want to use some basic formulas to show Totals, averages etc. However, the SUM function does not work on the data provided unless I select the cell (As if to edit the text) and the press enter. Is there a quicker way of selecting all of the cells in Column A for example and performing this function on mass, rather than selecting each cell individually. I have attahced a similar sample copy  My SUM formula is show in cell B15.
View Replies!
View Related
Convert Downloaded Web Page Numbers Seen As Text To Numbers
See attached file. A colleague is downloading rows of data from a website which contains a number field Excel is currently treating as Text after being pasted in. My spreadsheet includes just a sample of the many rows of data however as you can see the VALUE function refuses to convert these text values to numbers. How these might be converted and why the VALUE function refuses to work in this case?
View Replies!
View Related
Convert Numbers Representing Dates To True Dates
I import data from my works server and dates are represented in text. example text format is yyyymmdd/19790310. What I'm trying to do is add and subtract from this date. I was trying to utilize formula and havent got close. I have a couple of parameters: In column L I would like to represent the following if column "B" is "A" add 38 years to column "J" dates are represented as TEXT if column "B" is "B" add 35 years to column "J" " if column "B" is "C" add 30 years to column "J" " if column "B" is "d" add 30 years to column "J" " Column "M" I would like to find the difference in column "L" and "C" I know days will be difficult it can be represented in years and months only. I have no preference on whether I use VBA or a formula but have failed on on formula and was attempting to use VBA Case method but no success. I have 10000 rows of information and am open to any ideas attached an example of my data.
View Replies!
View Related
Convert Numbers As Text
Working with an inherited spreadsheet which has massive amounts of numbers formatted $US to 2 decimals. Can't manipulate the data: all formulas to sum groups of the numbers yield zero and formulas to multiply or divide them yield "#VALUE" error message. I guess the data is text (is left justified) although there's no apostrophe visible in the formula bar. Anybody know how I can convert these to numbers?
View Replies!
View Related
Convert Numbers To Text
converting numbers to text: In cell F39 i have a number (for example) 4322.08 and i would like to convert into into text in the following way In cell B47 I would like it to say Four In cell C47 I would like it to say Three In cell E47 I would like it to say Two In cell G47 I would like it to say Two In cell I47 I would like it to say Two in cell J47 I would like it to say Eight But essentially any number that I type into cell F39, I would like it to convert the number to text
View Replies!
View Related
Cannot Convert Numbers Stored As Text To Number Format
There is probably a really easy answer to this question but I cannot seem to figure it out. I need to calculate the sum of pages faxed on a fax report at my office on an excel spreadsheet which is downloaded from our server. It appears the file is not meant to be used in excel but it downloads as an "*.xls" file anyway. When I open the file I receive this message: "The file you are trying to open, 'FaxCetailReport.xls', is in a different format then specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?" The file is comnig from a trusted source so I select "Yes" and it opens just fine. There are just over 50 rows of info, one of them being the number of pages. In a blank cell I enter "=SUM(select rows)" and the result is a "0". I have converted the cells to the "number" format, no change. When I right click on the cells and select format, the protection tab on the right has the check box "Locked" checked and unchecking it does nothing, it is checked as soon as you go back in to the protection tab. The only way around this is to manually retype the numbers in a cell next to the pages column, which is ok for 50 numbers, but I have some sheets where there is over 1,000 entries.
View Replies!
View Related
Cstr Function: Convert A Range Of Numbers To Text
i am trying to convert a range of numbers to text using the Cstr function ,it doesnt work. excel VBA help states that CStr function converts a numeric value to a String. i am aware that i can convert a number by preceding it with an apostrophe i just need to know why Cstr doesnt do the job Sub covrt() For Each rng In Selection.Cells rng = CStr(rng.Value) Next End Sub
View Replies!
View Related
Convert Numbers Stored As Text To Text
I have the following macro that I use to convert numbers stored as text to text. For some reason, if I try to use it twice in a row, it won't work, I get an error that says "applicationdefined or objectdefined error" and highlights this part of the Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 1).Copy
View Replies!
View Related
Compare 2 Columns For Numbers In Mixed Text & Numbers
I need to compare two colums by number decription for example m344 in one column and fsh3441 in another. All I want to match is 344. In column a I want to indcate the match by placing an X by each match. View my attachment for reference. I don't know if it makes a difference but the columns are centered in my original spreadsheet.
View Replies!
View Related
Converting Decimal Numbers To Text With Dot Numbers
we work with both Lotus 123 and Excel 2003. Lotus will be gone next year, but for now, the official mean to publish our reports is Lotus. With my work, I copy/paste a Lotus page to Excel. I use the following macro to convert Lotus format numbers (which Excel considers as text) to real numbers: Sub ForceToNumber() Dim wSheet As Worksheet For Each wSheet In Worksheets With wSheet . Range("IV65536") = vbNullString .Range("IV65536").Copy .UsedRange.PasteSpecial xlPasteValues, xlPasteSpecialOperationAdd End With Next wSheet End Sub Source : http://www.ozgrid.com/forum/showthre...087#post184087. The problem is that I need to send back this data in Lotus. Excel considers decimal numbers with a coma as real numbers and numbers with a dot as a text. This previous macro fixes that. However, Lotus works the other way. Only numbers with a dot are considered real numbers. So I would need to find a way to code a macro that converts any numbers in the Excel sheet to a number with a dot. It's a bit like doing the opposite operation.
View Replies!
View Related
Match (text) Numbers To Numbers From One Cell To Another
I need the " solutions cells " to match to the " source cells " and return a " yes " or " no ". in cells d4:d10. Ex: #1 cell C4=01234598, it will conduct a search & match of cell A5 which contains the number 123, since cell C4 contains all 3 numbers from cell A5, then the formula would result in a " Yes ". The match has to contain all 3 numbers from the solution cells to the source cells. Ex: #2 cell C7=67891234, it will conduct a search & match of cell A8 =901. Since cell C7 only contains the 9&1 from cell A8, it would return a " No " because cell C7 did not contain all 3 numbers from cell A8. I'm not sure if the IF, Match, Index or Count formula is the right one to use, they all seem to partially work, but still don't complete this problem.
View Replies!
View Related
Convert Text Format To Numbers Format In Vb
how i convert text format into numbers format in vb. Currently i have a formula in vB: Private Sub Textbox3_Change() Textbox3 = Val(Textbox1.Value) + Val(Textbox2.Value) End Sub however..when i sum it up (in excel) using"=sum" formula...it ooes not sum up I faced an error "number stored as text"..how to i convert it to numbers format in vb.
View Replies!
View Related
Convert To Numbers
I have a workbook full of six digit numbers that I need to manipulate but can't. They have been datadumped from a server and copied as nonnumeric values. I have tried EVERYTHING I could find on how to convert these column cells to numeric fields: Multiplying by 1  didn't work VBA Code  For Each xCell In Range("a1:a1012") xCell.Value = xCell.Value Next xCell didn't work Format Cells as numbers  didn't work Format Cells as general  didn't work Remove Spaces  didn't work Text to Columns  didn't work This is how the data is stored. 000216 000703 000744 000819 002184 002529 002571 002573 002783 003222 003645 003826 004034
View Replies!
View Related
Convert Numbers
I need to create a formula to convert the number into millions. I want to do this in the format cell >> Custom in the excel sheet. Suppose the number is 2000000 the it should show as 2.
View Replies!
View Related
Convert Words Into Numbers
For my research in medicine I build a spreadsheet with google docs. After having received most of the data I transfered the spreadsheet into an excel file. The next thing I would like to do is to convert the words in the spreadsheet into numbers so I can calculate with them. For example: every row in column E consist of one of the two words: 'man' or 'woman'. I would like to convert the word 'man' into a '0' and the word 'woman' into a '1'. Is it possible to write a formula or function for this?
View Replies!
View Related
Convert Negative Numbers To Zero
In an excel column listing monthly payments, I cannot report a negative value . I need to "carry forward" the negative value by automatically adding it to the next positive cell in the column. This process should loop through the column leaving no negative numbers, leaving a zero in any cell where the negative number was originally and balance out to be the same amount as the sum of the original listing of monthly payments.
View Replies!
View Related
Convert Numbers To Times
I have a workbook that has many linked cells and formulas. Some of the times were entered manually in hh:mm format and are located on tables that are used to get a number from. On another worksheet, there are times taken from the internet that show up in general format. So 00:05 shows up as 5. I have tried to change these into number formats but it always shows up as 00:00 and tries to include the date in the formula bar. These various cells are linked and are used to look up other values but the formats are different and not talking. I am looking for a way to get them to work with eachother without using formulas if possible because many of the cells already have formulas in them.
View Replies!
View Related
Convert Standard Numbers To Time
Is it possible to convert the following data to an Excelrecognized time format (easily, as I have numerous millionrow files with time data like this). Some actual data is below with what it should be in parentheses. 0 (00:00) 1 (00:01) 13 (00:13) 57 (00:57) 145 (01:45) 308 (03:08) 900 (09:00) 1123 (11:23) 1334 (13:34) 2332 (23:32)
View Replies!
View Related
Convert Numbers To Single Letters
In row G, I have a wide range of values from 3000 up to 2500. These values are generated from a formula. =A5B5. I need a script that will scan row G between G5:G500 and give them a letter value. Convert anything less than 0 to an A Convert anything equal to 0 or equal to 1 to a B Convert anything between 2 and 7 to a C Convert anything greater than 7 to a D It can place the new categorized letter value over the original formula in row G or put the letter value right next to it in H.
View Replies!
View Related
Convert Positive Numbers To Negative
I have created a Pivot table from a source data file that includes a document status column ('Type I Or C') (I for Invoice & C for Credit) and an Invoice / Credit value column (Invoiced Price). However the values for both invoices and credits are positive so when summed distort the total values. I am attempting to create an additional column within the Pivot table to convert the credit note values to negative by inserting a calculated field under the formula tab, i.e. =IF('Type I Or C'="C",'Invoiced Price','Invoiced Price') The above formula works fine in a standard worksheet but it is not reckonsing the "C" within the pivot table. If I manually change the value to numeric (i.e. 1 for Invoices and 2 for credits) it works fine, i.e =IF('Type I Or C'="2",'Invoiced Price','Invoiced Price') so it appears to be an alpha issue?
View Replies!
View Related
Convert 4 Digit Numbers To Times
Is there any way changing general four digit number into hours and minutes? I have almost 31000 rows of imported data, showing arrival and departure times with four digits (dep 2130, arr 0130) without colons and I need to subtract dep from arr to get block hours. Is there a way to add colon to multiple cells or some other solution to my problem? Adding colon to 62000 cells one by one does not sound that good to me.
View Replies!
View Related
Convert Numbers To Postitive/Negative Based On Column Header
I have this data source file that comes from a back office program, and basically what i want it to do is to turn the number negative in column C when the header of that column in column A is WITHDR and leave it positive when it is CONTR. What i was thinking was that it could go to column A from column C and go up each cell until the cell is not a date and multiply the column C number by 1 depending the column A header equals WITHDR and do that for every cell in column C, but I dont know how to proceed, im new at VBA.
View Replies!
View Related
Convert Imported Text Dates To True Dates
I have a very large spreadsheet that comes out of SAP and it brings out the majority of cell entries as text ie putting a ' at the begining of any number. the problem that i have is with the cells that have a date in them ie '19/04/07. I have tried varioous methods to remove this and turn the cells into date format but to no avail. Also i do not get the option to convert text to number
View Replies!
View Related
Macro To Convert Numbers To Alphabet Letters & Sum Range
First my goal is to sum a row and column. For example i want the sum of row 1 a to row 1 g. However, I'm using a loop for an automated macro. So i want to be able to say something like cells(1, count), so during the loop count goes from 1 to 7 representing the letters in the alphabet. But I'm not sure how to do so. Secound, in my vba script to pull information from specific pages i say "sheet1.activate" how can i had the sheet switching during the process to speed up the calculation? and last but not least, If Cells(1, locd) = "2008" Then When i run my script, i get a runtime error 1004 applicationdefined or object defined error. The weird thing is, i can move what line it is on and then the macro will run ONCE and then it breaks again with the same error for that specific piece of code.
View Replies!
View Related
Finding Common (repeated) Numbers In Columns Of Numbers
I work for a charity and I have to cancel the donations of people whose credit card donations have been declined in three consecutive months. If in Column A I have a list of donor IDs whose credit cards were declined in Jan 2008, in Column B I have a list of donor IDs whose credit cards were declined in Feb 2008 and in Column C I have a list of donor IDs whose credit cards were declined in Mar 2008, is there a way of showing in a fourth column which donor IDs were common (repeated) in Columns A, B and C? I would have a title for each column in A1, B1 and C1, and also the column where the repeated donor IDs would be displayed.
View Replies!
View Related
Turning Red Font Numbers To Negative Numbers.
Usually this question is asked the other way around, but I have a somewhat unique problem. A certain website gives out tables filled with numbers. Positive numbers show in black font and negative numbers show in red font, but unfortunately, negative numbers do not include the minus sign  the font is red and that's it! I need a macro (or any other solution) that will turn the red font numbers to negative ones and would possibly format the cell to show negative numbers in red (I guess the last part is easier). The main problem is searching for the red font numbers and turning them negative.
View Replies!
View Related
Extracting Numbers From Alphanumeric Strings (strip Numbers?)
I import data from another program in order to evaluate it. Unfortunately, one of the fields I need contains copyright data, however, it has been very inconsistently entered into the database. For example, sometimes the data appears "c1999." or "1999" or "" or "[1999]" or even "19?" and also sometimes "1999, 1990" and many other variations on that. I discovered the link in the excel help file about extracting numbers from alphanumeric strings, but my situation is still too variable for it to apply; that file didn't take into account that alphanumeric strings don't always lump numbers and letters together. I was able to correct a few things, but my command of excel isn't knowledgeable enough to really come up with something effective. Some ideas I had that I don't know how to implement: is there a way to strip nonnumerical characters from an alphanumeric string? (I've been doing some find/replaces to get rid of some of it, but that is obviously not very efficient when I have to repeat this process daily.) Perhaps then I could just detect the first 4 numbers of the string somehow. However, that doesn't solve the problem of when a wild card is used as in "199?" or "20?" etc. Bottom line, I just need to grab the first four numbers that appear in the string (but NOT additional numbers that occur after a wild card or a space if the year was not completed in 4 numbers; in that case I'd just be happy with a null value). I've been doing this with a formula so far. My only experience with macros has been in simply recording them, not actually writing them, but I'll give anything a try.
View Replies!
View Related
Delete Numeric Series Numbers Between Numbers Entered
I want to ask that I have got a workbook with different number series i want user form where i can enter its start number and end number and then it finds and delete shift cells up said series number i have entered in user form please see mentioned below example. Series 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 and i want to delete 1 to 5 numbers delete to shift cells up.
View Replies!
View Related
