Excel String Functions and Other Tips

Here are a couple of functions that you can use to manipulate strings in excel. Note that there are many other string functions available in Excel, but the next presented are most common.

1. PROPER(text) - capitalize first letter of each words from a text
For example:
cell A1: "test text"
cell A2: =PROPER(A1) would return "Test Text"

2. UPPER(text) - upper each char from each word from a text
For example:
cell A1: "test text"
cell A2: =UPPER(A1) would return "TEST TEXT"

3. LOWER(text) - lower each char from each word from a text
For example:
cell A1: "test TEXT"
cell A2: =LOWER(A1) would return "test text"

4. LEFT(text, number_of_characters) - return the first number_of_characters characters from text text
For example:
cell A1: "test TEXT"
cell A2: =LEFT(A1, 3) would return "tes"

5. RIGHT(text, number_of_characters) - extracts a substring from a string starting from the right-most character
For example:
cell A1: "test TEXT"
cell A2: =LEFT(A1, 3) would return "EXT"

6. LEN(text) - returns the length of the specified string
For example:
cell A1: "test TEXT"
cell A2: =LEN(A1) would return "9"

________________________________________________

Now here are a couple of special combinations of the above functions to achieve different results:

1. Upper first character from a text and let the rest of them unchanged
=UPPER(LEFT(A1;1))&RIGHT(A1;LEN(A1)-1)
For example:
cell A1: "test text"
cell A2: =UPPER(LEFT(A1;1))&RIGHT(A1;LEN(A1)-1) would return "Test text"

2. Split a string into multiple columns using a token (string tokenizer)
2.1. Select the column or cell that you want to split
2.2. Make sure that you have enough blank columns in the right
2.3. Go to Excel menu -> Data -> Data Tools -> Text to Column
2.4. From the Wizard that appears select Delimited option and click Next button
2.5. In the next Wizard window select Delimiter -> Other: -> enter your own delimiter
2.6. Click Next to preview the data resulted and then Finish
For example:
cell A1: "test - text"
Follow the above steps and select the "-" delimiter
Would result:
cell A2: "test "
cell A3: " text"