When dealing with automatic messaging systems, we often have to use some conventions in our input files to signify the type of data we are entering into the system. For instance, text values need to be enclosed within double quotes to denote a string of characters. While using SheetKraft to generate the input files for such a system, we may have problems on generating double quotes due to absence of any escape sequence(s) / characters.
To generate the desired output file, we must go through the following steps:
As double quotes are used as an identifier for beginning and end of strings, they are not printed / shown readily in Excel unless kept solitary. However, if we require to show them in the resultant calculation, we can display them in various ways:
To append the double quote character to the start & end of our strings, we can use a variety of techniques, few of which are mentioned here:
CONCATENATE function and
& operator work similarly in that, they simply append data provided to them so they can be used to add the double quotes generated within one cell to the start and end of any other cell.
Suppose our data exists in cells A4 and we have generated double quotes as calculation result in cell B1, we can use either of the following to reach the required result:
1. CONCATENATE($B$1,A4,$B$1) 2. $B$1&A4&$B$1
Whichever one we use, it can later be filled down using
This is useful if we would like to encapsulate some data with double quotes. However, for a larger range, it is preferable to use JoinText.SK.
JoinText.SK function combines the text with the given delimiter. This is useful for appending double quotes to encapsulate multiple columns. It is recommended to be wary of data within columns while using this function as the existence of delimiter character (being used in
JoinText.SK) in column data can cause the function to go astray from the desired output.
Depending on how we export the data, we might face either of the following scenarios:
To deal with this, we can concatenate data from all the desired columns within a single column and then export that particular column using Export To Text while selecting the as-is option to allow the data to maintain its form.