SpreadEveryone: The Fetchland Excel wire

101 watchers

GlennR Oct 2019 12:29pm, 22 Oct 2019 45,774 posts	0 GlennR What exactly are you doing with that? TRIM() simply removes leading and trailing spaces.
GregP Oct 2019 12:30pm, 22 Oct 2019 115,477 posts	0 GregP My data has leading and trailing 'stuff' - tabs, mostly.
GlennR Oct 2019 12:33pm, 22 Oct 2019 45,776 posts	0 GlennR Filthy.
Neal Oct 2019 4:53pm, 22 Oct 2019 106 posts	0 Neal In the past, I learned that it can be easier to clean data outside of Excel. Also, in this case, replacing all the tabs in data with spaces may make life easier. Sometimes it can be too easy to resort to complicated formulae rather than take one or two manual steps and then use simpler formulae.
GregP Oct 2019 6:26pm, 22 Oct 2019 115,508 posts	0 GregP That's a great post Nealers. In this case it was absolutely pig-headedness on my part. There were eight (count 'em. eight) fields that needed cleaning. It would have been massively easier to do it in word. Less fun though.
um Oct 2019 10:58pm, 22 Oct 2019 1,312 posts	0 um Now I'm puzzled Greg - what would you do in Word that you can't do in Excel ? (around data cleansing, not the whole thing)
run free Oct 2019 11:09pm, 22 Oct 2019 3,587 posts	0 run free I can easily create a table of contents and table of tables with a couple of clicks And mail merge..... yeah
GregP Oct 2019 6:00am, 23 Oct 2019 115,513 posts	0 GregP See what whitespace characters are, for a start
run free Oct 2019 1:58pm, 23 Oct 2019 3,591 posts	0 run free Without having to refer to ASCII, you can easily replace formatting characters in Word. But it is a lot more fun to write a formula
flanker Oct 2019 1:40pm, 24 Oct 2019 18,676 posts	0 flanker I do all that sort of cleansing using a decent text editor (I have a few 😀) as they generally have far better search and replace, handling of non-alphanumerics, column modes, powerful macros, etc.