convert text to number value and keep text as text

I have a string of data that is numbers and text that all exports as text, but I want the numbers to say as number values and text to stay as text.

I tried this, but it doesn’t work.

If Not isNull({ord_ln.ordnum}) Then
If isNumeric({ord_ln.ordnum}) Then
Else {ord_ln.ordnum}

whitecastle (BOB member since 2019-03-12)

You did not say what you were exporting to. I am assuming Excel. Also what tool are you using to export (Deski, Webi, Crystal Reports)?

kevlray :us: (BOB member since 2010-06-23)

Sorry I’m exporting from Crystal Reports to excel. But I’m needing the logic written inside Crystal reports so when i export it to excel it is already formatted the way I need it.

whitecastle (BOB member since 2019-03-12)

[Moderator Note: Moved from General Discussion to Crystal Reports]

Marek Chladny :slovakia: (BOB member since 2003-11-27)

Unfortunately when exporting to Excel. Excel looks at the first few values and determines what it thinks the data type is. I have yet to figure out how Excel does this. Maybe someone else knows.

kevlray :us: (BOB member since 2010-06-23)

My issues is within Crystal Reprots not excel. I’m trying to write the code within Crystal Reports so when my report runs it converts a string data that is a number to values and text stays the same. It can get half of it I just can’t figure out the 2nd half.

for example:

Ord Number

I want the 123 to be converted a value and I want C5654 to stay as a text.

If Not isNull({ordnum}) Then
If isNumeric({ordnum}) Then
Else 0

The above converts the numbers to a value correctly, but it changes the C Ord numbers to 0 instead of keeping the original.

I tried using this instead, but it doesn’t like the last line it says it needs a value instead.

If Not isNull({YourStringField}) Then
If isNumeric({YourStringField}) Then
Else {ordnum}

whitecastle (BOB member since 2019-03-12)

The problem then is that you can have only one data type per field (expression). So you can either have the expression return a number or a string (of course other data types are available, but not relevant in this situation).

kevlray :us: (BOB member since 2010-06-23)

I agree with kevlray said but one thing you could try, as long as you only have two expected output types. My thought is something like this:

Formula 1: Checks for numeric value and returns the value as a number otherwise returns a 0. If zero is a valid number us an invalid number like -9999. [Return type numeric]

Formula 2: Checks for a non-numeric number and returns the values as a string. Otherwise returns a blank. [Return type string]

In this way you have two formulas so you can have two different output types. Now you set the formulas up to suppress if they have certain results. Formula 1 should suppress if it returns a zero (or whatever invalid number you choose). Formula 2 should suppress if it returns a blank.

In the designer if you place these two formulas on top of each other and make sure they are the same size they will produce either a number or string depending on the input value. I’m not sure how Excel will handle this but it should get you where you want to go in Crystal.



NifflerX (BOB member since 2009-08-09)

I did not think of that solution (even though I should have, I have some something similar in the past). Excel should be find, it will only see ‘one data’, how it interprets the data is yet to be seen.

kevlray :us: (BOB member since 2010-06-23)