Excel 2007 : Add space between characters and numbers in Microsoft Excel

**freerdj** · 06-04-2009, 07:27 PM

I am doing data scrubbing where I have ~8000 addresses where only some contain the following "errors" in the string:

10847Elk Run Rd
11712Jefferson Ave
11801Coastal Hwy

I need a way to search for "numtext" and replace with "num text", regardless of how long the leading number or following Latin string is. The ideal function would add spaces where necessary and avoid those that are formatted correctly.

10847 Elk Run Rd
11712 Jefferson Ave
11801 Coastal Hwy

Please advise.

**shg** · 06-04-2009, 07:34 PM

Try this:

=TRIM(LEFT(A1, MATCH(TRUE, MID(A1, {1,2,3,4,5,6,7}, 1)>"9", 0) - 1) & " " & MID(A1, MATCH(TRUE, MID(A1, {1,2,3,4,5,6,7}, 1)>"9", 0), 100) )

**freerdj** · 06-04-2009, 11:33 PM

No way, that is incredible. All hail!

**shg** · 06-05-2009, 10:33 AM

Would you please mark the thread as Solved?

**saketpadia** · 10-08-2015, 07:07 AM

Hi,

I have a similar issue. I too need to insert space between text and numbers, which are of varying length for eg.

ABC1234XYZ
AB12XY
ABCDE123456LMNO

I need it to be shown in the following manner.

ABC 1234 XYZ
AB 12 XY
ABCDE 123456 LMNO

I have some 2000 odd items which needs to be sorted in above manner. Please help

**Sam Capricci** · 10-08-2015, 07:26 AM

Saketpadia, you need to open your own post for your problem. If you believe this post is similar enough you can paste the link into yours as a reference. (Forum rules and all that.).