|
Split 5 digit number into 5 cells
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 |
Split 5 digit number into 5 cells
Enter this in A2 (or anywhere), and copy down:
=RIGHT(LEFT($A$1,ROWS($1:1))) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Forum Freak" wrote in message ... 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 |
Split 5 digit number into 5 cells
You could put this formula in A2 and copy down.
=MID($A$1,ROW()-1,1) HTH, Paul -- "Forum Freak" wrote in message ... 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 |
Split 5 digit number into 5 cells
WOW brilliant - that was much neater than a formula I saw a while ago.
Many thanks Kenny "PCLIVE" wrote in message ... You could put this formula in A2 and copy down. =MID($A$1,ROW()-1,1) HTH, Paul -- "Forum Freak" wrote in message ... 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 |
Split 5 digit number into 5 cells
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 -- "Forum Freak" wrote in message ... WOW brilliant - that was much neater than a formula I saw a while ago. Many thanks Kenny "PCLIVE" wrote in message ... You could put this formula in A2 and copy down. =MID($A$1,ROW()-1,1) HTH, Paul -- "Forum Freak" wrote in message ... 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 |
Split 5 digit number into 5 cells
Hello,
Or select A2:A6 and array-enter: =MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1) Regards, Bernd |
Split 5 digit number into 5 cells
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" wrote in message ... 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 -- "Forum Freak" wrote in message ... WOW brilliant - that was much neater than a formula I saw a while ago. Many thanks Kenny "PCLIVE" wrote in message ... You could put this formula in A2 and copy down. =MID($A$1,ROW()-1,1) HTH, Paul -- "Forum Freak" wrote in message ... 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 |
Split 5 digit number into 5 cells
Try this:
=INT(MID($A$1,ROW(A2)-1,1)) HTH, Paul -- "Forum Freak" wrote in message ... 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" wrote in message ... 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 -- "Forum Freak" wrote in message ... WOW brilliant - that was much neater than a formula I saw a while ago. Many thanks Kenny "PCLIVE" wrote in message ... You could put this formula in A2 and copy down. =MID($A$1,ROW()-1,1) HTH, Paul -- "Forum Freak" wrote in message ... 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 |
Split 5 digit number into 5 cells
Many thanks Paul it worked a treat.
Kenny "PCLIVE" wrote in message ... Try this: =INT(MID($A$1,ROW(A2)-1,1)) HTH, Paul -- "Forum Freak" wrote in message ... 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" wrote in message ... 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 -- "Forum Freak" wrote in message ... WOW brilliant - that was much neater than a formula I saw a while ago. Many thanks Kenny "PCLIVE" wrote in message ... You could put this formula in A2 and copy down. =MID($A$1,ROW()-1,1) HTH, Paul -- "Forum Freak" wrote in message ... 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 |
Split 5 digit number into 5 cells
=--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)
Regards, Bernd |
| All times are GMT +1. The time now is 02:59 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com