MS Query problem

[cjany]

I have an Excel spreadsheet that has a column with both numbers, such as 68, and a combination of numbers and letters, such as 1D. When querying this via Microsoft query, the records with the letters show up blank. It only pulls in the records with the numbers. The column in my original spreadsheet is formatted as "General". I've even tried changing to "text" and that didn't work. Any suggestions?

Cheryl

 

[Rofeu]

Hi,

not sure if that's the solution, but even though you format as text, the data is still a number. You might want to try using another column and put the formula =TEXT(Cell_Ref, "@") in. This will convert the number to text.

Like I said: not sure if this will fix your problem, but give it a shot.

Cheers,

Roel

 

[cjany]

That worked great!!!! Thanks so much.

 

[SkipVought]

MS Query cannot handle TEXT and NUMERIC in the same column. In fact, it a BAD table design to combine in a column.

CONVERT you NUMBERS to STRING by prepending a TIC, and your query will work.

FYI, fields like

Part Number
Employee Number
Invoice Number
ZIP Code

are IDENTIFIERS, not to be used in numeric calculations, and ought to be handled as STRING.

Skip,
[sub]
[red][/red]
[/sub]