Issue with Cell fomat

[mohan08]

Hi,

In the attached file i have 2 column with reference 1 and reference 2 which should be in Text format and used to extract data from Microsoft office database.
issue is i have to copy data from multiple excel files(Various version xls, xlsx and macro enabled) and save in excel 97-2003 workbook.
Even though values are shown in Text format. i will get error as 'Data type mismatch'.
to change it to text i have tried with 'Format painter' , On the Format Cells window select Text , updated ' to convert to text.

is there a way to check if resolve the error in excel.

 

[vletm]

mohan08
Those two cells are in Text-format.


Where do You get 'Data type mismatch'?
select Ignore Error
or
select Error Checkin Options...
and do wanted selection.

 

[mohan08]

Hi Vletm,

Where do You get 'Data type mismatch'? - after running database.

in the below screenshot you can se D2 cell is saved as text with ' but on the format cells window its in "General" format, C2 cell returns true for formula =istext(D2).



select Error Checkin Options...
and do wanted selection.

if possible can you let me know which field to check for format.

 

[pecoflyer]

It is important to understand that formatting is the way XL presents an underlying value to the outside world.
Dates, for instance, are only numbers to XL, but when you "ask" Excel to show it is a date it will do so. But that number still is the underlying value. ( you can have the same shoe red or black, but the shoe is still the same)
So, formatting text to numbers will do nothing.
How do you recognize text looking like numbers from real numbers?
If you do not apply manual alignment to a cell ( which you should avoid as long as you are building your sheet), text values align left in a cell, and real numbers align right. This allows you to see immediately what is wrong

 

[vletm]

mohan08
You've noticed - something - in front of Your 'number'?

If You'll take it away ... then it'll be a number.
Of course, sometimes numbers needs to use as texts ...

Where do You have or use ... database ... in Your sample file?

 

[mohan08]

Hi vletm,

i use the sample file to extract data from Database. the excel file is the input file and it will extract data from multiple table in Microsoft database.

 

[vletm]

mohan08
Do Your Your running database expect to use text or what kind of data?
Without clear details or sample, it's a challenge to guess.