Useful Text Formulas
Data downloaded from main systems (financial or otherwise) often comes in a less than ideal form and can contain:
- Extra spaces,
- additional characters
- Combinations of data e.g. codes and names in one text string
- Numbers downloaded as text
This makes the data difficult to use in a spreadsheet and if your data has one or more of these nuisance factors then it’s probably worth tidying up (or cleaning) the data before you use it throughout the rest of your spreadsheet. Fortunately, the excel text formulas can help do this especially if they are used in combination.
The default formatting of text in Excel is left aligned whereas for numbers the default formatting is right aligned. This default formatting will normally be applied to data downloads from systems so the alignment of your data can be a handy way of identifying whether you are looking at text or numbers although it is not 100% fool proof. Some systems will download numerical data as text (you can spot this as the numbers will be left aligned rather than the ususal right alignment for numbers) meaning that if you try to perform a mathematical calculation on it this will result in a value error. If text is all numbers the VALUE(cell reference) formula will turn this into a number.
The particular formulas covered on this site are LEN(), LEFT(), RIGHT(), CONCATENATE(), and TRIM().
Excel Monkey 