Text Functions in Excel

Conor Jordan
Jan 11, 2021
2 min read

Text functions allows users to extract pieces of text from selected cells, neaten the appearance of text in cells and bring together text from separate cells. The function Left extracts the left part of text within a cell depending on the number of characters the user specifies. The Right function extracts text in the left part of a cell depending on input. The Mid function extracts a set number of characters from the middle of a cell.

1. Enter the following table:

2. Select cell C4

3. On the Formulas tab in the Function Library group, select Text

4. Select Left

5. Select B4 for the Text textbox

6. For Num_chars type in 2

7. Click OK

8. This returns the first 2 letters of the Product Number

9. Select cell C5

10. On the Formulas tab in the Function Library group, select Text

11. Select Right

12. In the Text textbox select cell B5

13. For Num_chars type in 2

14. Click OK

15. This displays the last 2 letters of the Product Number

16. Select cell C6

17. On the Formulas tab in the Function Library group, select Text

18. Select Mid

19. Select cell B6 for the Text textbox

20. Type in 3 for the Start_num

21. Type in 4 for the Num_chars

22. Click OK

23. This has displayed the 4 numbers in the middle that started at the 3rd number

Trim

The Trim function can remove the spaces between text within a cell. This makes the appearance of text in a cell much clearer and is useful for neatening a range of cells in a worksheet.

1. Select cell C8

2. On the Formulas tab in the Function Library group, select Text

3. Select Trim

4. In the Trim text box, select cell A8

5. Click OK

6. This removes unnecessary spaces between words

Concatenate

This function takes a number of separate cells containing text and joins them together in another cell. This can be useful if a user has a list of contact details and wants the first name and surname of each contact to appear in a single cell.

1. Select cell C9

2. On the Formulas tab in the Function Library group, select Text

3. Select Concatenate

4. In the Text1 textbox, select A9