BusinessObjects Board

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
ToNumber({ord_ln.ordnum})
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
123
C5654

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
ToNumber({ordnum})
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
ToNumber({YourStringField})
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.

HTH.

-NifflerX


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)