Access Data Analysis Cookbook

Chapter 5. Working with String Data

Text-based data can contain more than just the letters of the alphabet. Numbers can be treated as text, and there are many characters that are neither letters nor numbers, but are vital to text-based work. Consider tabs, carriage returns, spaces, backspaces, and many of the symbols used in everyday work (hyphens, currency symbols, etc.). These all need to be as accessible and pliable as the letters and numbers.

In this chapter, you’ll find recipes illustrating how to find text strings within other text strings, how to replace text strings with others, and how to remove unwanted spaces from text strings. There’s also a recipe that discusses different methods for combining text strings, and one that reveals how to sort numbers that are stored as text.

Returning Characters from the Left or Right Side of a String

Problem

How can I isolate a certain number of characters at the beginning or end of a text string? Going a step further, is there a way to return the left and right portions of a text string based on a character found in the string itself?

Solution

The Left and Right functions return characters from the beginning and end of a text string, respectively. Both functions take two arguments: the string being addressed, and the number of characters to return. For example:

• Left("cat", 1) returns “c”

• Left("cat",2) returns “ca”

• Right("Apple Pie", 3) returns “Pie”

• Right("Apple Pie", 15) returns just “Apple Pie,” even though 15 characters were requeed—there are ...