### Numbers and strings

 Example files with this article: Numbers and strings

## Introduction

In Turbo Integrator processes and business rules, you regularly convert a string to a number and a string to a number. This can be a pain in the backside. Just think about loading actuals coming from different ERP systems, following different decimal separators, thousand separators, and so on. Or exporting data for Hyperion (for example, 7 decimals). And countless other situations.

Below is a chart on the 2 times 3 functions that we can use to do the conversions. It has got nothing revolutionary, but I find it interesting to have an overview that I can consult on a regular basis. Right, until the moment I know this information by heart (as opposed to, me thinking that I know this information by heart).

## Overview chart

#### From a number to text

• NUMBERTOSTRING (TI only)
- Arguments:
1. A real value

- Input: the decimal separator for the current user locale!
- Output: similar to the 'general' number format: no thousands separators and - for negative numbers

• NUMBERTOSTRINGEX (TI only)
- Arguments:
1. A real value
2. A format string
3. Decimal separator
4. Thousands separator

• STR
- Arguments:
1. A real value
2. The string length
3. The number of decimals

- Input: you must use . (period) as the decimal separator and , (comma) as the thousands separator
- Output: if necessary, the function inserts leading blank spaces to attain this length. Hence, use TRIM.

#### From text to a number

• STRINGTONUMBER (TI only)
- Arguments:
1. A string

- Input: the decimal separator for the current user locale!

• STRINGTONUMBEREX (TI only)
- Arguments:
1. A string
2. Decimal separator
3. Thousands separator

• NUMBR
- Arguments:
1. A string

- Input: you must use . (period) as the decimal separator and , (comma) as the thousands separator
- Output: All characters other than '0' through '9', '+', '-', ',', '.' and 'E' are ignored.

## Examples

Right, we got that! Now here are 3 examples of the usage. In the first example, we move 1 in a typical Periods dimension, using business rules: adding 1 to a period (string) as in 01, 02, 03, …, 12 (Rules). Note: Adding a Text attribute to the dimension is by far the better option.

```IF(NUMBR(!Rp_Period) < 9, '0', '') | TRIM(STR(NUMBR(!Rp_Period)+1, 2, 0))
```

In the second example, we will loop through a dimension with 24 forecast scenarios (spanning 2 years), Fct_01 until Fct_24:

```f = 1;
WHILE( f <= 24 );
vFctScenario = 'Fct_' | NUMBERTOSTRINGEX( f, '00', '', '' );

# other code

f = f + 1;
END;
```

You see that there is no need to check whether f is smaller than 9 or not, because then we need to add leading 0. The NUMBERTOSTRINGEX function takes care of this. The last 2 parameters in NUMBERTOSTRINGEX are empty since we are looping from 1 to 24 over whole numbers smaller than 1000.

Lastly, we will be reworking negative values with parentheses to a minus sign (Turbo Integrator coding):

```vAmount = '(123,45)';

vAmount = Trim(vAmount);
IF(SCAN('(', vAmount) = 1 & SCAN(')', vAmount) = LONG(vAmount) );
vAmount = '-' | TRIM(SUBST(vAmount, 2, LONG(vAmount) - 2));
ENDIF;
IF(SCAN('(', vAmount) > 0 % SCAN(')', vAmount) > 0 );
ITEMREJECT('Unknown data format for ' | vAmount);
ENDIF;

vAmount_N = STRINGTONUMBER(vAmount);
IF(vAmount_N <> 0);
# ...
ENDIF;
```

Right, that’s it for today!