Is there a way to split a set of numbers into different columns

**wgwg123** · 04-08-2011, 11:10 PM

Hi,

Good morning from S'pore. I am a novic excel user. Here is my question:

I have 10,000 sets of numbers, ranging from 0000 to 9999 keyed into 10,000 rows. Is there anyway to split these 10,000 sets of numbers into 4 columns?

For instance, column A containing 6789 (*note: no spacing) to be split into:

Column B: 6
Column C: 7
Column D: 8
Column E: 9

Thank you!

**teylyn** · 04-08-2011, 11:34 PM

Hello,
you can use this set of formulas

B1
=LEFT(A1,1)

C1
=MID(A1,2,1)

D1
=MID(A1,3,1)

E1
=MID(A1,4,1)

This will work if all numbers have 4 digits. If the a number has less than 4 digits, the columns to the right will be empty.

cheers

**wgwg123** · 04-08-2011, 11:59 PM

Hi Teylyn,

Thanks for your suggestion.

It works for most of the cases, except numbers beginning with zero. That means your suggested formulas doesn't apply to 0000 to 0009. Is there any way to solve this? Thanks again for your advice.

Regards,
Werner

**wgwg123** · 04-09-2011, 12:24 AM

Hi,

I'm referring to my second questions about the range of numbers from 0000 to 0009. I tried to input a 'sign before the first zero, and it works!

I would like to take this opportunity to thank Teylyn again.

Regards,
Werner