Split 5 digit number into 5 cells

[Forum Freak]

Hi everyone

I am sure I have seen this before but it is not showing up on Google.

A1 has a 5 digit number eg 12345
I want a formula or VBA code to produce
A2=1
A3=2
A4=3
A5=4
A6=5

anyone point me in the right direction?

Kenny
Win XP
Office 2003

 

[RagDyer]

Enter this in A2 (or anywhere), and copy down:

=RIGHT(LEFT($A$1,ROWS($1:1)))

 

[PCLIVE]

You could put this formula in A2 and copy down.

=MID($A$1,ROW()-1,1)

HTH,
Paul

 

[Forum Freak]

WOW brilliant - that was much neater than a formula I saw a while ago.

Many thanks

Kenny

 

[PCLIVE]

I'm glad you liked it. Keep in mind that this formula will only work if
placed in row 2. To be able to place the formula anywhere, then you would
need to modify it slightly. In fact, this will probably be the better
recommendation.

=MID($A$1,ROW(A2)-1,1)

Regards,
Paul

--

 

[Bernd P]

Hello,

Or select A2:A6 and array-enter:
=MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)

Regards,
Bernd

 

[Forum Freak]

Thenks for the help but I intended using these individual numbers in a
VLOOKUP however it did not work. I eventually found that they are not
actually numbers - I used ISNUMBER to prove this.

How can I convert them into numbers so the VLOOKUP will work?

Kenny

 

[PCLIVE]

Try this:

=INT(MID($A$1,ROW(A2)-1,1))

HTH,
Paul

--

 

[Forum Freak]

Many thanks Paul it worked a treat.

Kenny

Try this:

=INT(MID($A$1,ROW(A2)-1,1))

HTH,
Paul

 

[Bernd P]

=--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)

Regards,
Bernd