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

[Oswin]

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

 

[Franz Verga]

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

 

[SteveW]

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

 

[Gord Dibben]

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

 

[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

 

[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

 

[SteveW]

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