How do I split data in a column (formula)?
 

I did this a few years ago but have lost the formula.

I have a column of data. I want to take the last three digits of the data
from the column and put them in their own column. I know there's a fairly
simple forumla for this, but it's been a long time for me. Any help would be
appreciated.

=RIGHT(A1,3)

Vaya con Disos,
Chuck, CABGx3


"dan" wrote in message
...
I did this a few years ago but have lost the formula.

I have a column of data. I want to take the last three digits of the data
from the column and put them in their own column. I know there's a fairly
simple forumla for this, but it's been a long time for me. Any help would
be
appreciated.

Hi, thaks, but is there a way so it actually *cuts* the data from the
original cell and puts it in the new cell? exp:

a1 123456 and I want to take out 456 and put it in the next column

so it ends up being

a1 123 b1 456



"CLR" wrote:

=RIGHT(A1,3)

Vaya con Disos,
Chuck, CABGx3


"dan" wrote in message
...
I did this a few years ago but have lost the formula.

I have a column of data. I want to take the last three digits of the data
from the column and put them in their own column. I know there's a fairly
simple forumla for this, but it's been a long time for me. Any help would
be
appreciated.

Data | Text to Columns and follow the wizard.

Data TextToColumns follow the menus will work if your data is all the
same length, or separated by equal separators..........this formula will
give you the number but leaving off the rightmost three characters,
regardless of how many characters there are in the number..........

=MID(A1,1,LEN(A1)-3)

Vaya con Dios,
Chuck, CABGx3



"dan" wrote in message
...
Hi, thaks, but is there a way so it actually *cuts* the data from the
original cell and puts it in the new cell? exp:

a1 123456 and I want to take out 456 and put it in the next column

so it ends up being

a1 123 b1 456



"CLR" wrote:

=RIGHT(A1,3)

Vaya con Disos,
Chuck, CABGx3


"dan" wrote in message
...
I did this a few years ago but have lost the formula.

I have a column of data. I want to take the last three digits of the
data
from the column and put them in their own column. I know there's a
fairly
simple forumla for this, but it's been a long time for me. Any help
would
be
appreciated.

Thanks, but it doesn't work or I'm doing something wrong.

I want:

a1 b1
123456

to be:

a1 b1
123 456

I copied your formula and all it does is "copy" the numbes, but it doesn't
delete from the original column, so I get:

a1 12345 b1 345



"CLR" wrote:

Data TextToColumns follow the menus will work if your data is all the
same length, or separated by equal separators..........this formula will
give you the number but leaving off the rightmost three characters,
regardless of how many characters there are in the number..........

=MID(A1,1,LEN(A1)-3)

Vaya con Dios,
Chuck, CABGx3



"dan" wrote in message
...
Hi, thaks, but is there a way so it actually *cuts* the data from the
original cell and puts it in the new cell? exp:

a1 123456 and I want to take out 456 and put it in the next column

so it ends up being

a1 123 b1 456



"CLR" wrote:

=RIGHT(A1,3)

Vaya con Disos,
Chuck, CABGx3


"dan" wrote in message
...
I did this a few years ago but have lost the formula.

I have a column of data. I want to take the last three digits of the
data
from the column and put them in their own column. I know there's a
fairly
simple forumla for this, but it's been a long time for me. Any help
would
be
appreciated.

Sorry Dan, I've been away from my computer most of the day. What you are
asking for cannot be done with just formulas. One cannot have a number in a
cell and replace it with a formula and have that formula re-create "some" of
the number in the same cell. A cell can have either a number or a formula,
not both. As has been given in the responses, your numbers can be split up
into parcels and placed into other columns but not replace the original
number directly.

If you have 123456 in A1, then =MID(A1,1,LEN(A1)-3) in B1 will give you 123,
and =RIGHT(A1,3) in C1 will give you 456. Then, you can do Copy
PasteSpecial Values in place on columns B and C and then delete column
A.........this will give you an end result of what I percieve you are
looking for.....ie: 123 in A1 and 456 in B1.

If, on the other hand all your numbers are the same number of digits, then
you can achieve the same thing with the Data TextToColumns feature. This
method will not need any formulas and will stay within the two columns A and
B.

Or, there is probably some nifty piece of code that could do the job in
place without either of the above methods, but it's beyond my present skill
level in VBA

If this is something you have to do frequently, then either of the first two
methods can also be automated with VBA.

hth
Vaya con Dios,
Chuckk, CABGx3


"dan" wrote in message
...
I copied your formula and all it does is "copy" the numbes, but it doesn't
delete from the original column, so I get:

a1 12345 b1 345



"CLR" wrote:

Data TextToColumns follow the menus will work if your data is all
the
same length, or separated by equal separators..........this formula will
give you the number but leaving off the rightmost three characters,
regardless of how many characters there are in the number..........

=MID(A1,1,LEN(A1)-3)

Vaya con Dios,
Chuck, CABGx3



"dan" wrote in message
...
Hi, thaks, but is there a way so it actually *cuts* the data from the
original cell and puts it in the new cell? exp:

a1 123456 and I want to take out 456 and put it in the next column

so it ends up being

a1 123 b1 456



"CLR" wrote:

=RIGHT(A1,3)

Vaya con Disos,
Chuck, CABGx3


"dan" wrote in message
...
I did this a few years ago but have lost the formula.

I have a column of data. I want to take the last three digits of
the
data
from the column and put them in their own column. I know there's a
fairly
simple forumla for this, but it's been a long time for me. Any
help
would
be
appreciated.