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 wrote:
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


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


On Tue, 29 Aug 2006 17:23:01 +0100, Oswin
wrote:

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

Check out DataText 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

On Tue, 29 Aug 2006 09:23:01 -0700, Oswin
wrote:

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

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

"SteveW" wrote:

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


On Tue, 29 Aug 2006 17:23:01 +0100, Oswin
wrote:

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

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

"Franz Verga" wrote:

Oswin wrote:
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


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

Glad it worked, thanks for the feedback

Steve

On Tue, 29 Aug 2006 20:39:03 +0100, Oswin
wrote:

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

"SteveW" wrote:

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


On Tue, 29 Aug 2006 17:23:01 +0100, Oswin
wrote:

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