Difference between revisions of "Excel to Analytica Mappings/Text and Data Functions"
Line 5: Line 5:
 
</div>
 
</div>
  
−
= ASC(text) =
+
== 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.
 
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) =
+
== BAHTTEXT(number) ==
  
 
Analytica does not contain support the Thai (Baht) language.
 
Analytica does not contain support the Thai (Baht) language.
  
−
= CHAR(number) =
+
== CHAR(number) ==
  
 
Analytica equivalent:
 
Analytica equivalent:
 
  [[Chr]](number)
 
  [[Chr]](number)
  
−
= CLEAN(x) =
+
== CLEAN(x) ==
  
−
= CODE(text) =
+
== CODE(text) ==
  
 
Analytica equivalent:
 
Analytica equivalent:
 
  [[Asc]](text)
 
  [[Asc]](text)
  
−
= CONCATENATE(text1,text2,...) =
+
== CONCATENATE(text1, text2, ...) ==
  
 
Analytica equivalent:
 
Analytica equivalent:
Line 31: Line 31:
  
 
Also:
 
Also:
−
  [[Join]](A,I)
+
  [[Join]](A, I)
  
−
= DOLLAR(number'',decimals'') =
+
== DOLLAR(number'', decimals'') ==
  
 
Analytica equivalent:
 
Analytica equivalent:
Line 39: Line 39:
 
where the number format for the variable where this expression appears is set to currency with the indicated number of digits.
 
where the number format for the variable where this expression appears is set to currency with the indicated number of digits.
  
−
= EXACT(text1,text2) =
+
== EXACT(text1, text2) ==
  
 
Analytica equivalent:
 
Analytica equivalent:
−
  text1 = text2
+
  text1 == text2
  
−
= FIND(find_text,within_text'',start_num''), FINDB(find_text,within_text'',start_num'') =
+
== FIND(find_text, within_text'', start_num''), FINDB(find_text, within_text'', start_num'') ==
  
 
Analytica equivalent:
 
Analytica equivalent:
−
  [[FindInText]](find_text,within_text'',start_num'')
+
  [[FindInText]](find_text, within_text'', start_num'')
  
−
= FIXED(number'',decimals,no_commas'') =
+
== FIXED(number'', decimals, no_commas'') ==
  
 
Analytica equivalent:
 
Analytica equivalent:
 
  "" & number
 
  "" & 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.
+
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) =
+
== JIS(text) ==
  
−
No Analytica equivalent, since Analytica does not support double-byte characters.
+
No Analytica equivalent, since Analytica does not support double-byte characters.
  
−
= LEFT(text'',num_chars''), LEFTB(text'',num_chars'') =
+
== LEFT(text'', num_chars''), LEFTB(text'', num_chars'') ==
  
 
Analytica equivalent:
 
Analytica equivalent:
−
  [[SelectText]](text,1,num_chars)
+
  [[SelectText]](text, 1, num_chars)
  
−
= LEN(text), LENB(text) =
+
== LEN(text), LENB(text) ==
  
 
Analytica equivalent:
 
Analytica equivalent:
 
  [[TextLength]](text)
 
  [[TextLength]](text)
  
−
= LOWER(text) =
+
== LOWER(text) ==
  
 
Analytica equivalent:
 
Analytica equivalent:
 
  [[TextLowerCase]](text)
 
  [[TextLowerCase]](text)
  
−
= MID(text,start_num,num_chars), MIDB(text,start_num,num_chars) =
+
== MID(text, start_num, num_chars), MIDB(text, start_num, num_chars) ==
  
 
Analytica equivalent:  
 
Analytica equivalent:  
−
  [[SelectText]](text,start_num,start_num + num_chars - 1)
+
  [[SelectText]](text, start_num, start_num + num_chars - 1)
  
−
= PHONETIC =
+
== PHONETIC ==
  
 
No Analytica equivalent.
 
No Analytica equivalent.
  
−
= PROPER(text) =
+
== PROPER(text) ==
  
 
No Analytica equivalent.
 
No Analytica equivalent.
  
−
= REPLACE(old_text,start_num,num_chars,new_text) =
+
== REPLACE(old_text, start_num, num_chars, new_text) ==
  
 
Analytica equivalent:
 
Analytica equivalent:
−
  [[SelectText]](old_text,1,start_num-1) & new_text & [[SelectText]](old_text,start_num+num_chars)
+
  [[SelectText]](old_text, 1, start_num-1) & new_text & [[SelectText]](old_text, start_num+num_chars)
  
−
= REPT(text,n) =
+
== REPT(text, n) ==
  
 
Analytica equivalent:
 
Analytica equivalent:
−
  Index J:=1..n do Join( text,J )
+
  Index J:==1..n do Join( text, J )
  
−
= RIGHT(text'',num_chars''), RIGHTB(text'',num_chars'') =
+
== RIGHT(text'', num_chars''), RIGHTB(text'', num_chars'') ==
  
 
Analytica equivalent:
 
Analytica equivalent:
−
  [[SelectText]](text,[[TextLength]](text)-num_chars)
+
  [[SelectText]](text, [[TextLength]](text)-num_chars)
  
−
= SEARCH(find_text,within_text'',start_num''), SEARCHB(find_text,within_text'',start_num'') =
+
== SEARCH(find_text, within_text'', start_num''), SEARCHB(find_text, within_text'', start_num'') ==
  
 
Analytica equivalent:
 
Analytica equivalent:
−
  [[FindInText]]( find_text, within_text'', start_num'')
+
  [[FindInText]]( find_text, within_text'', start_num'')
  
−
= SUBSTITUTE(text,old_text,new_text'',instance_num'') =
+
== SUBSTITUTE(text, old_text, new_text'', instance_num'') ==
  
 
Analytica equivalent when ''instance_num'' is not specified:
 
Analytica equivalent when ''instance_num'' is not specified:
−
  [[TextReplace]](text,old_text,new_text,all:true)
+
  [[TextReplace]](text, old_text, new_text, all:true)
  
 
Analytica equivalent when ''instance_num'' equals 1:
 
Analytica equivalent when ''instance_num'' equals 1:
−
  [[TextReplace]](text,old_text,new_text)
+
  [[TextReplace]](text, old_text, new_text)
  
−
= T(value) =
+
== T(value) ==
  
 
Analytica equivalent:
 
Analytica equivalent:
 
  If [[IsText]](value) then value else ""
 
  If [[IsText]](value) then value else ""
  
−
= TEXT(value,format_text) =
+
== TEXT(value, format_text) ==
  
 
Analytica equivalent:
 
Analytica equivalent:
Line 126: Line 126:
 
where the number format for the variable containing this expression is set to the desired number format.
 
where the number format for the variable containing this expression is set to the desired number format.
  
−
= TRIM(text) =
+
== TRIM(text) ==
  
 
Analytica equivalent (in release 4.0.1 or later):
 
Analytica equivalent (in release 4.0.1 or later):
 
  [[TextTrim]](text)
 
  [[TextTrim]](text)
  
−
= UPPER(text) =
+
== UPPER(text) ==
  
 
Analytica equivalent:
 
Analytica equivalent:
 
  [[TextUpperCase]](text)
 
  [[TextUpperCase]](text)
  
−
= VALUE(text) =
+
== VALUE(text) ==
  
 
Analytica equivalent:
 
Analytica equivalent:
 
  [[ParseNum]](x)
 
  [[ParseNum]](x)
  
−
To use [[ParseNum]], you must add the Flat File Library to your model.
+
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.
+
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.

Revision as of 02:24, 6 January 2016

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 the Thai (Baht) language.

CHAR(number)

Analytica equivalent: 

Chr(number)

CLEAN(x)

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 (in release 4.0.1 or later): 

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.

Comments


You are not allowed to post comments.