Free Microsoft Excel 2013 Quick Reference

Imported Numbers Seen as Text

I am working with dbf files (GIS stuff) and when i import charts with numbers in them, the numbers show in excel as text.

I get those little green triangles at the top left of each cell. If i click on that, it asks me if I want to convert this to a number; so I can convert individual cells.

However, if I highlight a whole column of these, and I format the cells to number, it does nothing.

EDIT:

I cant add any colums (due to other program i am using), and I am trying to find the MIN number in a row - some of which are already numbers, others numbers stored as text).

Anyone know what is the deal here? Obviously there is a trick I am missing.

Thanks


I have opened a .txt (by right clicking and selecting Open with rather than open from within Excel) file with numbers using the format 1,234,567.00 but in my country we use 1 234 567,00. So I created a macro changing the , into "" and the . into , and it works fine while creating the macro itself (by using the macro recorder) and the numbers shows and behaves like numbers. However, when running the macro on a new file some numbers remain as text whereas others are numbers. Theres is no common string, eg that numbers starting with - (minus) remains text.

It does then not matter if I go to the format function and select number with decimals and thousand dividor - the number still remains as text. I have also tried the suggested multply by 1 but it still remains as text. The only thing that helps is to either double click on the number or use the Konvert text to number. But I would then have to click each number in the whole table which is what I wanted to avoid.

all suggestions are appreciated

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 non-intensive things. It calls the second macro because I wanted them seperate should I want to just run the second.

	VB:
	
 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 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

	VB:
	
 FixTextAsNumberError() 
     '
     'Initialize some variables used in the loop
    StartRow = Selection.Row 
    StartCol = Selection.Column 
    LastRow = (StartRow - 1 + Selection.Rows.Count) 
    LastCol = (StartCol - 1 + Selection.Columns.Count) 
     
    Col = StartCol 
     
    Do While Col

I immediately began having problems with formulas and pivot tables when I began importing data from an offsite postgreSQL database. For example, "countif" and "sumproduct" formulas invariably return values of 0. I can create pivot tables from the imported data, but nothing with a numerical value will group.

After fighting with this for literally hours, I finally noticed that all numbers in the columns were on the left side of the column meaning that the numerical data is being imported into Excel as text, even though the columns in the spreadsheet into which the data is imported are in number format.

I have to figure out a way either to import numbers as numbers from the postgreSQL database, or to convert text to numbers after the data is imported.

Can anyone help?

Thanks.

Hi

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

Andrew

Hi al

Please 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.

Can anyone offer any advice on how these might be converted and why the VALUE function refuses to work in this case?

TIA,

Steve Stewart

Background of problem:

Data the I receive is in the form of numbers stored as text. (Must keep significant figures)
I track this with a formula to return the significant digits..

The final form of the data should be stored as numbers as text. Currently using Fixed() function recalling the significant digits from the beginning.

Problem:
Ok my dilemma, the final thing I need to do is conditional formatting of the data using numbers.

Example of single data entry
Step 1'324.24 - Received from importStep 2Determine digits beyond decimal point =2Step 3Use Value() to turn into number.
324.24000Step 4Apply Conditional format. (if < 350 and > 300 then green text and Underline)Step 5 Reapply Significant Digits from Step 2 using Fixed(324.240000,2)
324.24The issue is once I change the cell back to text the conditional formatting fails.

Thank you in advance.

I have a set of data that I'm exporting from a program into excel. Once exported, the numbers in column AJ all appear left aligned with the green corner arrow indicating they are "numbers stored as text". I'm trying to figure out how I can format the sheet so that any time i export data into it, the data will automatically convert from "numbers stored as text" to numbers, without the user having to do anything. I'm not very familiar with code or macros, so I would need a solution explained in step by step terms. Thanks!

Hi,

I have to deal with some numbers that sometimes begin with one or more zeros.

(example)

12345
012345
0012345

Those are all different numbers in the system.

When using formulas this causes problems, because even if I set the cell type to "text," existing columns of numbers do not automatically convert to numbers stored as text. I literally have to double click on every single cell in the column to actually convert the cell context to text.

-I have tried =CONCATENATE("'",(A1)) , but that just returns a '012345.
-I have tried every paste special command

For this specific task I am just trying to use vlookup, so I need to have the cell formats match for it to work.

If there is another formula or way of doing this I am all ears.

Thanks,

James

If I use Vlookup to find something when:
Lookup Value is formatted as General and
Lookup Array is formatted as Text
Excel will find the thing I'm looking for.

If I use Vlookup to find something when:
Lookup Value is formatted as Text and
Lookup Array is formatted as General
Excel will NOT find the thing I'm looking for.

Does anyone know why?

Also, how do I get around this problem when doing a VLookup?

Jim

Hi,

I am using a macro do display actual date, by copy pasting special, so that the day and month is always 2 digits: ie. 01, 02, etc. in case less than 10.


	VB:
	
 Workbook_Open() 
    Sheets("Sheet2").Range("C4").Select 
    ActiveCell.FormulaR1C1 = "=TEXT(TODAY(),""dd"")" 
    Range("D4").Select 
    ActiveCell.FormulaR1C1 = "=TEXT(TODAY(),""mm"")" 
    Range("E4").Select 
    ActiveCell.FormulaR1C1 = "=TEXT(TODAY(),""yyyy"")" 
    Range("C4:E4").Select 
    Selection.Copy 
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
    :=False, Transpose:=False 
    Application.CutCopyMode = False 
    Range("A4").Select 
    Sheets("Sheet2").Range("C4:D4").NumberFormat = "00" 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I paste special so the user does not see this formula. However, there is that annoying notification saying it is a number stored as text. What is the universal way to switch this warning off (or to better write the macro) so this warning will not be on any version of Excel?

Thanks,
Helen

Hi,

I'm trying to find out if there is a cell or validation property that will tell me whether or not a cell contains a number stored as text.

Now for the details. I have a column of serial numbers. Some of the serial numbers are both alpha and numeric; some are just numeric. ALL of the cells in the column are formatted as text (the numeric serial numbers have 10 or 15 spaces padded on to the end). In Excel 2003, if you select one of the numeric serial number cells, the top left corner of the cell has a visual indicator (green triangle), and the exclamation point sign appears next to the cell. Clicking on the exclamation point, I get a subcontext menu with the first selection saying "number stored as text". If Excel knows this, I would think there would be a property in this regard I could call from a macro and have a boolean value returned telling me it is or is not a number stored as text.

Does anyone know if such a property exists, and how I can call/use it to determine what serial numbers are text, and which are numbers stored as text? Or is there a better way of separating which are numeric and which are alphanumeric, keeping in mind they all come in as text?

Thank you one and all in advance.

Larry

when i open up this particular worksheet all the account numbers have the numbers stored as text errors on them

how can I get rid of this, they are all numbers and if i click on one and then click on the function bar above and then click on another cell it will convert it for me. or if I click on the error i can convert that way.

i want the whole column converted. changing the format doesn't do anything

Hi,
I'm looking for some code to convert numbers stored as text to numbers.

Can anyone help me with this please?

Thanks

I am using VBA to get a number stored as text on one sheet put into a variable and using that variable to put down on another sheet
It always comes out as a number
I have formatted all columns as text
I have used formula
ilocn = activecell.value
locn = format(ilocn,Text)

doesn't work
Theo

Hi,

I am after a wee bit of help in using Vlookup.

I am trying to lookup a number stored as text and return a value based on a table where the same number is stored as a number.

This formula allows me to lookup numbers or text if the stored table number is also text, but not if the stored number in the table is a number.

=IF(ISNUMBER(MATCH(B4,E2:E11,0)),VLOOKUP(B4,E2:F11,2),VLOOKUP(""&B4,E2:F11,2))

Is there a way to tell vlookup to interepret the first column as text?

Can anyone help.

I am having a problem with numbers being stored as text in Excel. I am
exporting the data from a SQL Server dB using a DTS package. I have tried
converting the fields before export in DTS and this does seem to work
temporarily, but the Excel file will eventually convert to all text.

I have read the help regarding converting these fields from "numbers stored
as text" to numbers, and have forwarded that information on to the end users.
However, this can be only a temporary solution, as the end users do not want
to do this every day.

Thanks in advance for any help!

Help!! i'm going mad

how do you Convert numbers stored as text to numbers useing a formula
(normaly comes up with and error..... but not today)

Thanking you

I've several xref tables in a spreadsheet that I decode using vlookup
I've used the following which worked...
=VLOOKUP(TEXT(DlrID,"0000"),DealerNameXREF!A2:B467 2,2,FALSE)

however when I edit the formula suddenly the cell contents become the
formula and not the formula value

using Edit> Replace> I can replace specific parts of the formula to what I
want and that had worked

but a new version of the XRef tables (which I copied in as seperate
worksheet) which has all numbers formatted as text (to preserve leading
zeros) isn't allowing me to create functioning formulas

even =3+4*8 in a cell displays as "=3+4*8" and not the value

ctrl + ` doesn't change anything related to the cell formulas I'm working with

where is the sheet parameter that I'm missing that may have been inherited
from the copied in worksheet?

most of the lookup references are based on numeric values zero padded and
treated as text
--
Jim

When you've got numbers, changing the format still leaves them as numbers,
not text (so no little green corners).
You can handle the difference within your lookup formula, without having to
change the data. If your lookup_value (the first argument) is text and the
table you're looking into has numbers, you can use =vlookup(value(a1),.....).
If it's the opposite, so that the lookup_value is numeric and the table is
text (with green corners), you can use =vlookup(trim(a1),....).
To change an entire column of numbers to their text equivalents, I'd use a
helper column. If your numbers are in A1:A20, then in B1 use the formula
=trim(a1). Autofill that formula down to row 20, then copy/paste values and
delete the original data in column A.
The reverse is easier. If you've got text that you need to turn into
numbers, copy a blank cell, then highlight your data and edit > paste
special, select 'add' and click ok.
HTH. --Bruce

"naclu" wrote:

> I've received help here with a VLOOKUP question and it's worked great for me
> so far... Thanks Max!
>
> New question. In the current VLOOKUP that I'm doing, one of my source files
> has the little green corners on the cells that tell you that numbers are
> stored as text.
>
> In the second source file, the cells are formatted to show as numbers stored
> as text, but the green corners are not there.
>
> I'm getting a lot of "no matches found" and I discovered that if I go to the
> second source file, double click on the cell like I am going to edit it, hit
> enter and move on, the corner is there all of a sudden and the VLOOKUP
> formula works.
>
> Is there any way to make those show up without clicking on each and every
> one of them?
>
> Thanks again in advance to anyone that might be able to help!

Ok, I tried using the kb article for this, but obviously I'm still doing
something wrong.... please help if you can!

I have a number 121101 in a cell -- when I right click the cell and select
format cells it says it's formatted as a date, but the error checking says
it's a number stored as text.

I select the option in the error checking that says convert to number.
I right click the cell again and it shows the format as general...
I change it to date and the number 121101 becomes the date 7/24/2231
I want it to become 12/11/2001.

Can someone please help me. I'm obviously not very good at excel and this
is making me crazy!!

Thanks in advance!!

I have number stored as text. It has a small green triangle at the upper
left of the cell. If I click on the cell it has a yellowsign with a ! and if
I click on that a menu and "convert to number"
This works fine one cell at a time.

I need to convert columns the cell format has no effect on the column of
cell I need to change. I did un click the protect on the last tab.

Suggestions?
Sueanne

I have numbers stored as text. I don't consider this to be an error, but
Microsoft Excel does, and puts a little green triangle in the upper left
corner of the cell. I can click to ignore the error, thereby hiding or
removing the green triangle, but after I save the document (type xml) and
retrieve it, the error returns. How do I permanently disable the green
triangle from appearing.
--
Kevin

I'm trying to write a macro but one column show those little green things
that shows that I have an Error (Number Stored as text ). I don't know a lot
of VB so I'm recording and touching here and there the VB. I cannot record
the comand that changes it to number when I click in the little thing in the
cell that contains the error. If I go to format cell and change it to number
the error stays there. The problem is that if I have that error VLOOKUP does
not work properly. Does anybody could understand what I tried to say? Can
somebody help me?

Thanks,

Marcelo

I wish there was an option with VLOOKUP so you could tell it to treat numbers
stored as text and plain text numbers the same.
The problem I run into is when I convert numbers stored as text to numbers I
may have a 00956 and a 956 which are two different quantities but are treated
the same when I convert them and the vlookup will return the wrong info.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...et.f unctions