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

- Imported Text Files Numbers Seen As Text
- Convert Numbers, Seen as Text, To True Numbers
- How to fix numbers when imported into Excel as text
- Convert Numbers Seen As Text To True Numbers
- Convert Downloaded Web Page Numbers Seen As Text To Numbers
- Conditional formating of numbers saved as text
- Auto-convert exported values in a single column from "Number Stored As Text"
- Converting numbers to numbers stored as text
- VLookup #N/A! When number seen as text
- Remove the "Number stored as text" error
- Boolean Property for "Number stored as text"?
- Number stored as text help
- Vba code to convert number stored as text to number
- Number stored as text - variable - number stored as text on
- Vlookup to lookup a number stored as text against a number.
- Numbers stored as text
- Convert numbers stored as text to numbers
- Vlookup formula editing based on numbers treated as text
- Numbers stored as text causes problem with VLOOKUP
- Need help converting Number stored as Text to Date
- Number stored as text
- How do I ignore the "Number stored as text" error message permane.
- Number Stored as text Error
- VLOOKUP should compare numbers stored as text to plain numbers.

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

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 SubIf 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

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.

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

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

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

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

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: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?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 SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Thanks,

Helen

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

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

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

Can anyone help me with this please?

Thanks

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

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.

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!

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

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!

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!!

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

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

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

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