NOTE: Many features on the XLplus Web site require Javascript. You can enable Javascript via your browser's preference settings.

XLplus Add-On: Convert Characters

This tool supports you converting characters of a single cell or a range of cells. Convert Characters not only converts values but allows converting formulas as well without loosing the formula.

Selection:

By default, the tool will convert all cells of the active selection. In case you wish to use different cells, simply select the cell or range of cells.

Cell content to convert

Specify, which cell content shall be converted.

Change values only	This will only convert all cells containing a constant value - no formulas will be changed.
Change formulas, too	This will additionally convert all formulas of the selection except array formulas. Conversion of formulas will extend the given formula by formulas which either convert the first character to Upper- or Lowercase or Trim the cell result. This conversion retains a formula within the cell. The cell gets longer due to the added formulas. In case the resulting formula exceeds the maximum possible length of a formula, the formula will not be converted. An information about all of these cells will be given then. All possible conversions can be applied simultaneously, i.e. you can convert a cells content to Uppercase plus Trim its content.
	Ex.: The cell contains the following formula: =If(A1>B1,"Greater","Smaller") Converting this to: Convert first character + Uppercase + Trim will result in: =Left(Upper(Trim(If(A1>B1,"Greater","Smaller"));1)&Mid(Trim(If(A1>B1,"Greater","Smaller")),2,Len(If(A1>B1,"Greater","Smaller")))
Replace formulas by value	This will first convert the formula of a cell to its value - the formula is lost then! Afterwards the wanted character conversion is performed. This option is not recommended since you loose your formula. It is only available since you might prefer a properly designed result and certain formulas cannot be converted due to their resulting length. Tip: In case you wish to properly design your sheet for printout, create a copy of the worksheet and convert characters then. This keeps your main spreadsheet incl. formulas intact. Note: This option will always convert a formula into its value regardless what has been specified additionally. You can use this feature for converting cells to values without having to Copy and Paste As Value.

Options:

By default, no selection is made. Selecting nothing will revert all changes which have been applied by this tool to any formula. Use this to remove any conversion applied to a formula.

Ex.: You have applied conversion to the formula used in the previous example. The formula now shows the first character in Uppercase, its content is trimmed, additionally. You now wish to remove all formatting again. Simply select Change formulas, too and do not specify Convert Characters and Trim. The formula will be converted back to =If(A1>B1,"Greater","Smaller").

Note: This is one of the major advantages of this tool - it retains your formulas and you stay flexible.

You can, at any time, specify conversion settings. This might change a formula or cell value from all Uppercase to first character lowercase, remove the Trim option of a formula or to add the Trim functionality to a cell value converted.

When specifying Convert Characters you can choose whether to convert only the first or all characters to Upper- or Lowercase.

Note: You have to activate Convert Characters before you can further specify conversion settings like whether to convert only the first or all characters.

Notes:

Since possibly changing your formulas, we have thoroughly tested the tool.

The following formulas were converted without any problems:

=IF(A1>B1;IF(B1>C1;"A1 größer C1 und B1";IF(A1>C1;"A1 größer C1, B1 kleiner C1";"A1 kleiner B1"));IF(B1
=INDEX($F$1:$F$5;MATCH(2;$G$1:$G$3;0);1)
=IF($G$1>$G$2;"grösser";"kleiner")
=" "&A1&B1&" tausend"

The tool will only convert formulas, which report back a string. All formulas reporting a number will not be changed. Note: This does not count for option Replace formulas by value - setting this will convert every function to its value!

The tool will only run into an error in case a converted formula would exceed the maximum possible length. Those formulas will be skipped, then, and stay unchanged.

Since array formulas normally report numbers instead of strings, array formulas will not be touched at all.

Versions:

Ver.: 2.0.20 and Ver.: 3.0.20 correct a bug where full-trimming values did not correctly full-trim.