Question on spacing out characters

**Alabaster_W** · 09-09-2011, 01:28 PM

I have a large amount of zip codes to space out for a personal project I am attempting to do. I have all the zip codes, but I need them spaced out in another cell.

For example: I want 45365 to become 4 5 3 6 5 in another cell

How would I go about adding a space between all characters in a single cell?

**shg** · 09-09-2011, 01:44 PM

Welcome to the forum.

=TEXT(A1, "0 0 0 0 0")

**Alabaster_W** · 09-09-2011, 02:12 PM

Originally Posted by shg

Welcome to the forum.

=TEXT(A1, "0 0 0 0 0")

Thank you for having me. I appreciate your quick response.

Is there an alternate formula that I can use?

A large portion of the data I found is presented with commas and the ending of some of the zip codes (ex. as 48077,78,87) and some of the data is presented as a series (ex. as 48008-12). The majority, however, is presented as a single zip code and converts perfectly.

Is there a formula to space out everything in a given cell rather than space out just five characters?

**shg** · 09-09-2011, 05:06 PM

If you get rid of the miscellany so that the cell values are numbers, you could use

=TEXT(A1, REPT("0 ", LEN(A1)))

If the cell values are text (as some of yours are), it would take either a very messy formula or a simple UDF (VBA).