Separating Zip Codes

Question

I have some data with zip codes separated by a comma. I know I can separate the values by "Text to Columns", however, I am having an issue with the zip codes that start with zero.

For example, this is how my data looks

And then when I use text to columns it comes out like this

I know I can change the output to come as text, which can fix it when its a few zip codes

But I have some data points with hundreds of zip codes like that. I was wondering if anyone knew of a better method to separate these values, but maintain the 5 digits of the zip code.

Thanks

Answer 1

I dont know if all you want is to "see" the zip code with Leading zeroes. If that is what you are trying to do, simply apply a format to your result cells - that will display any 4 digit number, with leading 0s. You have to set a custom formatting to display the leading zeroes by entering 5 0s in the number format Type box. See the custom format in picture below; Custom Excel Formatting

Answer 2

So, this works using left() mid() & right with find() - quick and dirty though, I will let you exercise your talents to deal with 4 codes...