ExcelExportCleaner ( text )
Returns the specified text in a format that is safe to export.
Be the first to rate this function Log in to vote
|
Marcus Nilsson - Show more from this author
Square Moon https://www.squaremoon.se/ |
Function definition: (Copy & paste into FileMaker's Edit Custom Function window)
/**
* ==================================================================
* ExcelExportCleaner ( text )
*
* PARAMETERS:
* @text (text) Text value
* RETURNS:
* (text) An export friendly text
* EXAMPLE:
* ExcelExportCleaner ( "Hi Marcus.¶¶My name is…" ) returns "Hi Marcus. My name is…"
* DEPENDENCIES:
* None
* AUTHOR:
* Marcus Nilsson, Square Moon Industries AB, marcus@squaremoon.se
* PURPOSE:
* Returns the specified text in a format that is safe to export.
* NOTES:
* none
* REVISIONS:
* 2017-04-10, Marcus Nilsson. Created.
* 2017-04-11, Marcus Nilsson. Redesigned the custom function and made it recursive.
* 2017-04-11, Marcus Nilsson. Added "Char ( 10 ) = "¶" = LF = Line feed.
* ==================================================================
*
*/
Let (
[
~patternCount1 = GetAsNumber ( PatternCount ( text ; "¶" ) ) ;
~patternCount2 = GetAsNumber ( PatternCount ( text ; Char ( 13 ) ) ) ;
~patternCount3 = GetAsNumber ( PatternCount ( text ; Char ( 8232 ) ) ) ;
~patternCount4 = GetAsNumber ( PatternCount ( text ; Char ( 10 ) ) ) ;
~patternCountTotal = GetAsNumber ( ~patternCount1 + ~patternCount2 + ~patternCount3 + ~patternCount4 )
] ;
If ( ~patternCountTotal = GetAsNumber ( 0 ) ;
text ;
// Else
Let (
[
~substitutedText = Substitute ( text ;
[ "¶" ; " " ] ;
[ Char ( 13 ) ; " " ] ; /* Char ( 13 ) = "¶" = CR = Carriage return */
[ Char ( 8232 ) ; " " ] ; /* Char ( 8232 ) = "¶" */
[ Char ( 10 ) ; " " ] /* Char ( 10 ) = "¶" = LF = Line feed */
)
] ;
ExcelExportCleaner ( ~substitutedText )
)
)
)
Comments
Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.