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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
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
 
Back
Top