Posts tagged function

Did you know that you can put line breaks inside excel cells? It’s quite straight forward, within the cell just click ALT + Enter to make the line break. An example is if an address is entered in a single cell:

Personally it’s not something that I would use often as it makes the data harder to extract and analyse, but what happens if you have a spreadsheet has these incell line breaks, and need to break them out into columns? In the example here you may want to do an analysis on the geograhic spread or want to do a mail merge so want a list with columns for towns, counties etc.

If the lines were in separate cells you could use the paste special > transpose option to convert vertically listed data items into horizontal and vice versa however in this instance it won’t work as all the data is in a single cell. Instead what we need to do is remove the line breaks.

Using ALT + ENTER means that Excel inserts a line feed character, or ASCII CHAR(10) which shows as a new line. We need to remove these instances of CHAR(10) which appear invisible on screen and replace them with something else to act as a delimiter which we can then use to break the data into columns.

• To start with add a new helper column on your spreadsheet, in this instance it would be column B and type the formula =SUBSTITUTE(A1,CHAR(10),”£”)
• Fill this formula down against the rows that you have. This formula searches the cell indicated (eg cell A1) for all instances of CHAR(10) and substitutes them with £
• You can then use the Text to Columns function to break the data into columns using £ as the delimiter. You can of course use any character you like rather than the £ however make sure that it’s something you know doesn’t already appear in your data as otherwise the columns may break in the wrong places. And watch out for using the wildcard characters too (see my previous post on searching for wildcard characters)

The other alternative is to use find and replace to identify the line feed breaks which may be more appropriate if you have a large dataset with line breaks within the cells that need removing in a number of different columns. across the spreadsheet. However if you open the find and replace dialog box (CTRL + H) and type ALT + ENTER in the find box what happens? Erm, yes nothing – it just moves to the next box. Instead in the find box hold down ALT and type 010 then move the replace box and type in what you want the delimiter to be – eg £. Click Replace all and it should do the same as above but without the helper column.