Splitting a Cell in Excel

Introduction

We've all had this problem where there is something hiding in a cell in Excel and it's making out life difficult. A delegate to my latest online course had exactly this problem and he shared it with me, hoping I could help him out.

I solved the problem, of course, but it was a longer road than I expected it to be!

The Problem

The problem is that H receives some of his data via email: someone along the line copies tables from somewhere into an email message and sends it. H then copies the table from the message and pastes it into Excel.

It looks alright, of course, but once I got an example of the problem, I could see that each cell containing numbers, also contained leading and trailing spaces ... or some other unseen characters.

What I did

The TRIM() function, eg TRIM(C3) did not work

The CLEAN()  function, eg CLEAN(C3) did not work

The TRIM(CLEAN())  function, eg TRIM(CLEAN(C3)) did not work

Find & Replace did not work … when using one or more ordinary space characters typed from the keyboard.

Data … Text to Columns did not work

 

Those steps took just a few minutes but by this time I realised that I needed to know what was in the cells I wanted to split. This is how I decoded the cell contents: numbers in cells C3, C4 and D4:

 =TEXTJOIN(",",TRUE,CODE(MID(C3,SEQUENCE(,LEN(C3)),1)))

 =TEXTJOIN(",",TRUE,CODE(MID(C4,SEQUENCE(,LEN(C4)),1)))

 =TEXTJOIN(",",TRUE,CODE(MID(D4,SEQUENCE(,LEN(D4)),1)))

That told me the contents of cells C3, C4 and D4: in code form. For example, cell C3 contains the codes

160,160,160,160,160,160,32,49,53,44,52,48,48,32

cell C4 contains the codes

160,160,160,160,160,160,160,49,53,44,53,48,48,32

and cell D4 contains the codes

160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,52,50,44,51,49,55,32

How does that help? Knowing that 160 = space and 32 = space, it gives me a clue as to how to split the cells

OK, it didn't! It didn't help in this case at all!

 

I tried Flash Fill in Excel but I wasn't convinced it was the solution I needed IT MIGHT HAVE BEEN BUT it gave me an odd response to cell C4 and since I only had two rows of data to work on, that led to my uncertainty.

So, I didn't trust Flash Fill in Excel BUT IT MIGHT BE THE ANSWER. Try it and see if it works for you. 

The Solution That Works

In the end, I used Flash Fill in Power Query to solve this problem

Convert the data range into an Excel table and then From Table/Range, create a Query

For this example, Transform … Trim was not available to me, so I used flash fill in Power Query.

Select a column

Add Column … Column from Examples … From Selection … then enter the values you want to see … make any corrections

This worked for me for both of the columns that contained values

I then removed the original columns with numbers in, leaving the corrected columns and the text based columns.

Conclusion

The real problem here is in copying and pasting data from an email message: it's best if you don't do that. On the other hand, I imagine we have just about all copied and pasted tables from a web page with equally frustrating results.

The major frustration comes, though, when all of the functions and utilities built into Excel don't work, TRIM(), CLEAN() and everything else I tried. The good news from this blog post, though, is that I have provided you with some kind of checklist you can follow to solve any similar kinds of problems you might face.

Using the step to find out which are the leading characters should have been a winning idea but even that led me nowhere. More than that, I found that they used TWO kinds of spaces in the data: character 160 and character 32! Even though I knew that, it didn't help!

Still, at the end of it all, I found and used the solution built into Power Query and it is relatively easy to use.

No files to download

Duncan Williamson

13th October 2023