Hello, everyone.



This question pertains to a spreadsheet that I created by exporting a FileMaker database to .xlsx format.

The database has an "Auto Enter Upon Creation" Serial Number field.

Recently, I purchased Office Home & Student 2010, and decided to export the database into Excel. I did that easily and successfully with FileMaker.

When I open the .xlsx file in Excel 2010, the numbers in the Serial Number column have a little green triangle next to them.

Take a look:



I just want to know what that little green triangle is. That's all I really want! Thank you! Jd

It's a result of a background error check Excel performs for you (this setting can be turned off in the options if you want).

The green triangle indicates that Excel has spotted an error. If you left-click on the cell so as to select it, an exclamation mark icon will appear. If you click on the exclamation mark a small window will appear. The first line in the window tells you the error and the subsequent lines give you options to fix it, get more information about it, etc...

In this case, I'm pretty sure that you have numbers stored as text. That means that they look like numbers (double data type) in the cell, but really they are stored as text (string data type). This might cause problems if you use them for calculations in worksheet functions so it might be a good idea to convert them to numbers.

Attached Images

Convert to number.PNG (5.9 KB, 14 views)

Thank you for the helpful reply. A follow-up question...is there a way to batch-convert ALL of the text to numbers? I'm trying to figure out how to do that right now, but haven't succeeded yet.

As of right now, I do not intend to do calculations with this spreadsheet. But who knows?

Thank you again. Jd

Sure, using the mouse, if you select the all cells with the green triangles from the top downwards, you can convert them all in one go.

Hi Colin.

That worked, thank you.

But this leads me to one more question, if I may.

What I originally tried to do was select the entire column (by clicking the top section marked H) and then try to convert to numbers. I wasn't able to do that. If I had, oh, several hundred records--or even a thousand--it would take a long time to drag the mouse down the entire column.

Is dragging down the column, as per your instructions, the only way to do this?

I'm assuming you cannot select the entire column the way I want to because of the header row? Is that correct? Why, then, would I not be able to highlight the entire column by hiding the header row?

Just curious about that. Thank you once again. Jd

Hi Jd,

It looks like the way the Excel developers designed it is that, when you select the cells in that way, the first (visible) cell must have an error in it. The first, visible cell of the selection in this situation is the activecell.

So, if you select the whole of column H and if H1 has an error then it should be okay because H1 would be the activecell.

If H1 doesn't have an error value but H2 does, then hiding the whole of row 1 and then selecting the whole of column H should also do the trick because H2 would be the activecell.

Hope that helps...
Colin

The green triangles don't actually say there's an error, just what Excel thinks is an inconsistency.

To convert a 'numerical text' range to numbers:
• input a 1 in an empty cell
• cut the number
• select the range to be modified
• choose Paste > Paste Special > Values > Multiply > OK
Note: you can select a whole column for this, but any empty cells will end up with 0s in them and any cells containing formulae will be converted to their results (ie the formulae will be lost). Text will be unaffected.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

If you press Ctrl-End, that will take you to the last active cell in the worksheet. Pressing the Home key will then take you to column A. Of course, you can navigate to the left by scrolling. You can also scroll up from the bottom if you don't want to include the very last row.

To select from there to the top of the column, hold down the Shift key and press Ctrl-Home and scroll to the desired column. Keeping the Shift key depressed, you can also scroll down from the top of the column to another row if you prefer - and you can span more than one column if desired.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Hi Paul,
I think I'm within my rights to call them "errors", given that the Excel developers called this feature "Error Checking" and the green triangle is defined in the Excel helpfile as an "Error Indicator". But I do like the term you've used there: "inconsistency".

Congrats on the promotion to Super Moderator. By the way, are you going to the summit next month?

Cheers,
Colin

MS MVP - Excel

Thanks Colin,

Unless MS cares to send me a big fat cheque to cover return airfares from Australia, I very much doubt I'll be getting to any summit.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]