Move Partial Data from One Column to Another

S

schu1272

I am looking for a way to take 5000 lines of data in Excel and take th
last 6 digits of the data and move it into another column.

For Example: Take the last 6 digits and move to next column
Data 1 Data 2
435490870985430984 | 430984
234083209842309820 | 309820
432958739287193872 | 193872
542389034098430945 | 430945

With over 5000 lines to move I need an automated process so that i
does not need to be done manually, any help or ideas would be greatl
appreciated
 
P

Peo Sjoblom

Are the numbers of the same length (your example show 18 digits which have
to be text) If so, make sure the column to the right is empty and do
data>text to columns
and select fixed width, click next and select the last 6 digits and click,
then click next and select
the format if you want text or general and click finish (takes less than a
minute) and you can record a macro

--
For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom
 
K

Ken Wright

IF, all data is the same length then simply select the column, do data / Text To
Columns / Fixed Width / Set your marker before the last 6 characters and hit OK.
If not all the same length then post back.
 
K

kkknie

You asked for the last 6, but your example was the first 6. Either way
it's pretty easy.

For the first 6:

=VALUE(LEFT(A1,6))

For the last 6:

=VALUE(RIGHT(A1,6))

Put this in the column to the right (and change the A1 to the bi
number you are extracting numbers from). Then copy to all othe
columns. When you are done, select the entire column and choose copy
then paste special and select Values to remove the formula and replac
with actual values.

This solution converts the 6 digit number from text to a number. I
you wish to leave it as text, remove the VALUE function.
 
Top