moving numbers between columns without moving words

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

Guest

I have a long spreadsheet where I have already inserted many names with numbers in column A. I would like to move the numbers into a new column B, and leave the names in column A. My sister went on last night and someone gave her the formula to do this for each row. Is there a way to do it for a large spreadsheet per column? I won't be able to do it per row as its thousands of rows. ex; column A has Sam5 in row1; John10 in row 2; Harold16 in row 3.

I tried using text message under data to split, but the problem with that is the names are different lengths, and then I cut both letters and numbers into column B.

Thanks in advance.
 
if I understand it right you want that at the end you have, i.e. Sam in
column A and 5 in column B??? Now the following would only work if the names
are the same length but maybe (even though it is maybe a bit of a work
around but still faster than cut and paste each single one) it will help:

sort the names so the ones whith the same length are underneath each other,
mark them and then go:

Data --> Text to Column ---> click on fixed width ---> create a line where
you want to split the data by clicking on the position in the bottom --->
next --->
next ---> click finish

maybe I will find something a bit easier but for the moment that seems the
best and fastest solution, especially because you have so many names.

Papadapa said:
I have a long spreadsheet where I have already inserted many names with
Click to expand...
numbers in column A. I would like to move the numbers into a new column B,
and leave the names in column A. My sister went on last night and someone
gave her the formula to do this for each row. Is there a way to do it for a
large spreadsheet per column? I won't be able to do it per row as its
thousands of rows. ex; column A has Sam5 in row1; John10 in row 2;
Harold16 in row 3.
I tried using text message under data to split, but the problem with that
Click to expand...
is the names are different lengths, and then I cut both letters and numbers
into column B.
 
If all of the numbers are 1 or 2 digits long you can use the following two formulas (formulae?).

=IF(ISNUMBER(RIGHT(A1,2)*1),LEFT(A1,LEN(A1)-2),LEFT(A1,LEN(A1)-1))

=IF(ISNUMBER(RIGHT(A1,2)*1),RIGHT(A1,2),RIGHT(A1,1))

The first formula will return the name and the second will return the number. The IF function check for the length of the number. You can enter these in two helper columns and then Copy and Paste_Special>Values into columns A and B. If you have numbers with more then 2 digits or have entries with no number post back and I can redo the formulas.

Good Luck,
Mark Graesser
(e-mail address removed)

----- Papadapa wrote: -----

I have a long spreadsheet where I have already inserted many names with numbers in column A. I would like to move the numbers into a new column B, and leave the names in column A. My sister went on last night and someone gave her the formula to do this for each row. Is there a way to do it for a large spreadsheet per column? I won't be able to do it per row as its thousands of rows. ex; column A has Sam5 in row1; John10 in row 2; Harold16 in row 3.

I tried using text message under data to split, but the problem with that is the names are different lengths, and then I cut both letters and numbers into column B.

Thanks in advance.
 
Back
Top