| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Split 5 digit number into 5 cells
=--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)
Regards, Bernd |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Need to split a 17 digit number into 17 cells by paste function. | Excel Discussion (Misc queries) | |||
| How to validate a cell to insert from 9 digit number to 13 digit number. | Excel Worksheet Functions | |||
| How to validate a cell to insert from 9 digit number to 13 digit number. | Excel Worksheet Functions | |||
| Color a single digit in a mult-digit number cell | Excel Discussion (Misc queries) | |||
| When we enter a 16 digit number (credit card) the last digit chan. | Excel Discussion (Misc queries) | |||
Linear Mode
