Text To Column Split for Merged Zip Codes

[blackystrat]

Hello

I have a spreadsheet where the zip codes have been merged with other numbers making them invalid

For example, the actual zip code should be 37027 but for some error the ZIP appears as 370275378

I would like to know how I can split these numbers after five characters so that 370275378 would be split up as 37027 in one column and 5378 in the other

If i could even enter a delimiter such as a hyphen after the first 5 digits, I can then go ahead and split them up.

Please help

Thank you very much

 

[Weazel]

you can use data...text to columns...fixed width....as long as its always the first 5 numbers, you'd just need to insert the deliminator line after the 5th number

 

[Rick Rothstein]

Select the column, click the "Data" tab on the Ribbon, find the "Data Tools" group and click the "Text to Columns" button. On the dialog box that appears, click the "Fixed width" option button, then click the "Next" button. On the next dialog page, click the numbered line at the 5 mark (so that a vertical line appears with 5 digits to its left), then click the "Finish" button.

 

[blackystrat]

you can use data...text to columns...fixed width....as long as its always the first 5 numbers, you'd just need to insert the deliminator line after the 5th number

Yes! How could I not try that before posting here

Thank you so much for your prompt help

 

[blackystrat]

Select the column, click the "Data" tab on the Ribbon, find the "Data Tools" group and click the "Text to Columns" button. On the dialog box that appears, click the "Fixed width" option button, then click the "Next" button. On the next dialog page, click the numbered line at the 5 mark (so that a vertical line appears with 5 digits to its left), then click the "Finish" button.

Thank you so much Rick. That worked perfectly

Deeply appreciate all of your help

 

[Weazel]

you're very welcome

 

[iliauk]

Is it one long cell?

If so, try this

=MID($A$1,5*ROWS($1:1)-4,5)

and drag down

Edit: Ah, too late

 

[blackystrat]

Is it one long cell?

If so, try this

=MID($A$1,5*ROWS($1:1)-4,5)

and drag down

Edit: Ah, too late

Thanks a lot for your help iliauk

That formula might come handy some other day.

Much appreciated