irregular spacing


i have a data list with irregular spacing between each character. is there an easy way to clean this up?

for example: " C o u l d   w e   w i t h   i n k   t h e   o c e a n   f i l l , "

5 Replies



Given how letters work to make up words, that could become

Could wewi think theo ceanfi ll?

In another word,


how about if we could make a formula that could scavenger the characters and remove the character(space) immediately to the right?


It wouldn't be a formula; would be a VBA routine (or macro).


But " " is itself a character, and how will you differentiate between that, (" "), and "   " and "    " -- without the aforementioned dictionary to let you know that you've finished a word, the desired word, the word that was intended in the original randomly spaced sequences of letters and spaces, given that some words contain words, and some endings of words can combine with beginnings of others to make up new words.....


The point: mechanically removing spaces--especially when your original premise was that the spacing is irregular between characters--requires some artificial intelligence to recognize not only words but also meaning (from the context of the words that precede and follow any given sequence of letters that might, or might not, constitute a word)....


Your request might appear to be a simple one--and is, for a human being--but, like driving, is far more than a mechanical task.

actually, i did find a very simple way to do that, sorry to bother you about it. All i did was find and replace the triple space with some other character, then remove the single space, and finally replaced the temporary character with a single space. It worked very good.
Oh, yes, "Search and Replace" / AKA "Find and Replace" can work, but would be tedious at best, and dubious given your original description of irregular spacing. Maybe that spacing was less irregular than I took it to be. I use (in Word) Find and Replace, using Replace All, to make paragraph spacing consistent when it's not.....

Anyway, glad you got it resolved.