Can't pull "text" from Excel cells because of mix of #'s & letters 1

[boardburner]

In CR9;
I am trying to get part descriptions (formated in excel as text) into a report but because for some reason when CR reads them it thinks they are numbers and it eliminates anything that is a mix of numbers & letters.
Is there a way to force CR to read as text ??
I have tried "ToText" in formula to no avail. I am unsure why?

Thanks
G

 

[mbarron]

Crystal assumes that the first record's format, is the format for the entire column.

One way to trick Crystal is to put a dummy record in the first location that has the format you want the entire column to emulate and then eliminate that item using the select expert.


Mike

 

[boardburner]

Perfect....worked great.

Thanks
G

 

[boardburner]

Just as a follow up type question, What would I do in the future if some of the records were all numbers, not mixed alphanumeric??

Thanks
G

 

[mbarron]

What do you want to do with the number?

The reason for my question is because Crystal doesn't support (at least through version 8) "mixed" if then's. For example you can't have the true be a number result and the false be a string. The following would give an error of "A number is required here"

if {field} = "Something" then 1234 else
"Not a number"

An option would be to create two fields and suppress the false field.
Field 1 would be (you can use this field for summarizing, except for summaries that use a count of the records such as Count or Avegage):

If isnumeric({your.field}) then tonumber({your.field}) else 0

Field 2 (the string result) would be:
If not(isnumeric({your.field})) then {your.field} else ""

You would then suppress the fields using:
Field1 - not(isnumeric({your.field}))
Field2 - isnumeric({your.field})




Mike

 

[boardburner]

Actually just display it because it is just a gage identification. If I pull in the spreadsheet records with the excel cells formated as text and the first record just numbers not mixed, CR reads them as #'s and does not display the mixed identifications in my details. If I use your suggestion and put a "text" first record.......CR doesn't display the "all number" identification in the details. Again, just need to show what is there...all numbers or mixed.

Thanks
G

 

[mbarron]

Here is a macro that I use in Excel that converts selected numeric cells to text format. It ignores formulas and dates.

Sub nums_to_text()
'
'
'
Dim lop2 As Range

For Each lop2 In Selection

If IsDate(lop2) = False Then
If lop2.HasFormula = False Then
lop2.NumberFormat = "@"
lop2 = Format(lop2, "")
End If
End If
Next


End Sub

Let me know if you need to know how to add this to Excel.



Mike