Excel to Analytica Mappings/Text and Data Functions

This page shows how Excel Text and Data functions map to Analytica equivalents

ASC(text)

Analytica's Asc function is not an equivalent to Excel's ASC function. Excel's ASC function converts double-byte character sets to single-byte character sets. Analytica has no support for double-byte character sets.

BAHTTEXT(number)

Analytica does not contain support for the Thai (Baht) language.

CHAR(number)

Analytica equivalent:

Chr(number)

CODE(text)

Analytica equivalent:

Asc(text)

CONCATENATE(text1, text2, ...)

Analytica equivalent:

text1 & text2 & ...

Also:

Join(A, I)

DOLLAR(number, decimals)

Analytica equivalent:

"" & number

where the number format for the variable where this expression appears is set to currency with the indicated number of digits.

EXACT(text1, text2)

Analytica equivalent:

text1 == text2

FIND(find_text, within_text, start_num), FINDB(find_text, within_text, start_num)

Analytica equivalent:

FindInText(find_text, within_text, start_num)

FIXED(number, decimals, no_commas)

Analytica equivalent:

"" & number

where the number format for the current variable is set to fixed format with the indicated number of decimals, and thousands separators checked or unchecked as desired.

JIS(text)

No Analytica equivalent, since Analytica does not support double-byte characters.

LEFT(text, num_chars), LEFTB(text, num_chars)

Analytica equivalent:

SelectText(text, 1, num_chars)

LEN(text), LENB(text)

Analytica equivalent:

TextLength(text)

LOWER(text)

Analytica equivalent:

TextLowerCase(text)

MID(text, start_num, num_chars), MIDB(text, start_num, num_chars)

Analytica equivalent:

SelectText(text, start_num, start_num + num_chars - 1)

PHONETIC

No Analytica equivalent.

PROPER(text)

No Analytica equivalent.

REPLACE(old_text, start_num, num_chars, new_text)

Analytica equivalent:

SelectText(old_text, 1, start_num - 1) & new_text & SelectText(old_text, start_num + num_chars)

REPT(text, n)

Analytica equivalent:

Index J := 1..n do Join(text, J)

RIGHT(text, num_chars), RIGHTB(text, num_chars)

Analytica equivalent:

SelectText(text, TextLength(text)-num_chars)

SEARCH(find_text, within_text, start_num), SEARCHB(find_text, within_text, start_num)

Analytica equivalent:

FindInText( find_text, within_text, start_num)

SUBSTITUTE(text, old_text, new_text, instance_num)

Analytica equivalent when «instance_num» is not specified:

TextReplace(text, old_text, new_text, all:true)

Analytica equivalent when «instance_num»' equals 1:

TextReplace(text, old_text, new_text)

T(value)

Analytica equivalent:

If IsText(value) then value else ""

TEXT(value, format_text)

Analytica equivalent:

"" & value

where the number format for the variable containing this expression is set to the desired number format.

TRIM(text)

Analytica equivalent:

TextTrim(text)

UPPER(text)

Analytica equivalent:

TextUpperCase(text)

VALUE(text)

Analytica equivalent:

ParseNum(x)

To use ParseNum, you must add the Flat File Library to your model.

You can also coerce a text string to a number in other ways. For example, Evaluate(x) will do it. You can also specify a parameter to a User-Defined Function as x : coerce number, which will cause the input to be coerced.

See Also

• Excel to Analytica Mappings