in a column of numb's and names, put the numb's in other colum

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

Guest

I have over 2000 lines of a tag # number plus a dog name in one column in
excel 2003 which I scanned in. Can anyone tell me if I can edit the numbers
into another column automatically??? The entire column in one edit
operation??? The tag # into another column. Other than re-typing each line.
Thank you in advance for your help,
Oswin
 
Oswin said:
I have over 2000 lines of a tag # number plus a dog name in one
column in excel 2003 which I scanned in. Can anyone tell me if I can
edit the numbers into another column automatically??? The entire
column in one edit operation??? The tag # into another column.
Other than re-typing each line. Thank you in advance for your help,
Oswin
Click to expand...


Hi Oswin,

Do you have space between numbers and names? How many digit do the numbers
have?

If I assume you have a space between numbers and names and that your data
are in column 1 starting from A2, you could use this formula in an empty
cell:

=value(left(A2,find(" ",A2)-1))

If I assume you have 5 digits numbers and no space between numbers and names
and that your data are in column 1 starting from A2, you could use this
formula in an empty cell:

=value(left(A2,5))

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy
 
As long as the data is in some consistant format
like number space name
or each number is of a fixed width
The click on the column, Data, Text to Columns...

Then either split the text on space or after a particular column in the
data

Remember it will overwrite the columns to to the right
suggest you add in a couple of blank ones - delete unwanted ones afterwards

Steve
 
Oswin

Check out Data>Text to Columns.

Delimited by space?

Fixed width?

Might be something there you could use.

If you had supplied a small sample of your data it would have helped.


Gord Dibben MS Excel MVP
 
Hello Steve,
The first time I tried your suggestion within my file and I believe because
the scanned data file, I had a little problem. After I copied the 2200
record column into a test file, everything worked out just great. I had a
bit of work correcting the data, but it was nothing compared to if I had to
first all the numbers manually and then remove the numbers from the name
column.
I want you to know, I really appreciated your input to my problem!
Thanks again,
Oswin
 
Hello Franz,
Thank you very much for your reply. After reading the two replies I had
received, I tried Steve W's first and it worked fine for me. I will try your
suggestion the next time. Again, thank you very much for your suggestion.
Oswin
 
Glad it worked, thanks for the feedback

Steve

Hello Steve,
The first time I tried your suggestion within my file and I believe
because
the scanned data file, I had a little problem. After I copied the 2200
record column into a test file, everything worked out just great. I had
a
bit of work correcting the data, but it was nothing compared to if I had
to
first all the numbers manually and then remove the numbers from the name
column.
I want you to know, I really appreciated your input to my problem!
Thanks again,
Oswin
Click to expand...
 
Back
Top