Adding spaces to text data in a cell

[ParisFan43]

I am an Excel Beginner and need help adding spaces to text within a database-style spreadsheet with customer names and addresses (street, city, state, zip) that I am trying to clean up to create a mailing list. This information has been compiled from an outside program and saved as an Excel spreadsheet. There are thousands of customers within this spreadsheet.

I am using Excel 2007.

Often times, the text for a customer's street address will look something like this:

1234nw121st
789mainst
9510windingridgeroadn

I need a way to separate the text to look like this when finished:

1234 NW 121 St
789 Main St
9510 Winding Ridge Rd N

Can someone explain to me (in layman's terms please!) how I would go about doing this? I tried to separate the data using "Text to Columns" but since the program thinks the text is one long word, it didn't split it apart into multiple cells for me.

Any and all help would be greatly appreciated.

Thanks!

ParisFan

[outofthehat]

The only way you can add spaces automatically is to define a set of rules, then write formulae to implement those rules. If you think about the mental rules you're applying in the 3 examples you list, they are roughly
a) to put a space in if:
1. numbers change to letters
2. you recognise a distinct word (NW, Main, St, Winding, Ridge, Rd, N - and the rules for recognising the words are quite complex, e.g. not every n should be converted into N for North)
b) to capitalise
3. First letter of most words
4. Whole of compass points

Writing formulate for 3 is fairly straightforward, but 1, 2 and 4 are rather more difficult! 2, especially, requires referencing a lot of possible words!

I'm afraid that, unless you can come up with some more straightforward rules, you can't do the lot with formulae. You could get some way, by putting spaces around 'St' and 'Rd', but you'd still run into problems with words containing either or both of these letter pairs, e.g. 'stardust'.

Unless you've got thousands of rows, I'm afraid it might be easiest to do it manually...