If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
moving numbers between columns without moving words
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. |
#2
|
|||
|
|||
moving numbers between columns without moving words
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" schrieb im Newsbeitrag ... 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. |
#3
|
|||
|
|||
moving numbers between columns without moving words
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_SpecialValues 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 ----- 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. |
Thread Tools | |
Display Modes | |
|
|