There are lots of cases where manipulating text in different ways can be quite challenging in Excel. Learning a few formulas and techniques can help you to overcome these challenges.
A common problem is distinguishing whether a cell value is in number or text format. You may have values that look like numbers but when you try to sum them it doesn’t work because they’re actually text. A general rule is that numbers are aligned to the right and text to the left, but that isn’t always the case. To be certain, you can use the TYPE function, which returns 1 for numeric and 2 for text values.
The FIND function can help you search to see whether a particular word or phrase is included in a cell. This opens up lots of possibilities with text analytics e.g. if you have a customer comments field and wanted to search for the word ‘complaint’ you could use:
It can also help you split out text from a cell. In this example, with the help of LEFT, RIGHT and LEN we find the space in the name so we can split it into forename and surname.
Splitting out a middle name is a bit more complicated:
=LEFT(RIGHT(B3,LEN(B3)-FIND(” “,B3,1)),FIND(” “,RIGHT(B3,LEN(B3)-FIND(” “,B3,1)),1))
And to get the surname in this instance we also need to enlist the help of the SUBSTITUTE function:
=RIGHT(B3,LEN(B3)-FIND(“#”,SUBSTITUTE(B3,” “,”#”,LEN(B3)-LEN(SUBSTITUTE(B3,” “,””)))))
Text to Columns
Text to Columns is an excellent in-built tool to help you manipulate text. Going back to the customer comments field, if you wanted to split out each word into a different cell so you could pivot the field into a word count here is how you could do so:
Highlight the field(s) in question, go to the Data tab and within Data Tools you’ll find Text to Columns. In this case we’re going to choose Delimited as our option to split each field.
Next we’re going to choose which character(s) to choose as the delimiter. In this case if there any tabs, commas or spaces we want Excel to move to the next cell.
The final step allows you to edit the data types of each new column and then you can output the results of your Text to Columns: