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
|
|||
|
|||
delete numbers in each cell in same column
Is there an easy way to just delete the first two numbers in each cell all
the way down the column? i.e. 123456789 to 3456789? Would I have to create a helper column; put in a formula for the removal of the first two numbers then cut and paste on the column I want to adjust? If so, can you tell me specifically how to do this and why I am this specific formula to achieve this action....... Thank you! |
#2
|
|||
|
|||
delete numbers in each cell in same column
With your data in Col A try the below in B1 and copy down as required
=MID(A1,3,LEN(A1))+0 If this post helps click Yes --------------- Jacob Skaria "Anna Huber" wrote: Is there an easy way to just delete the first two numbers in each cell all the way down the column? i.e. 123456789 to 3456789? Would I have to create a helper column; put in a formula for the removal of the first two numbers then cut and paste on the column I want to adjust? If so, can you tell me specifically how to do this and why I am this specific formula to achieve this action....... Thank you! |
#3
|
|||
|
|||
delete numbers in each cell in same column
You could use a formula=RIGHT(A6,LEN(A6)-2) and copy down in a helper
column. OR sub droptwo() mc="a" 'column A for i=1 to cells(rows.count,mc).end(xlup).row cells(i,mc).value=right(cells(i,mc),len(cells(i,mc )-2) next i end sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Anna Huber" Anna Huber @discussions.microsoft.com wrote in message ... Is there an easy way to just delete the first two numbers in each cell all the way down the column? i.e. 123456789 to 3456789? Would I have to create a helper column; put in a formula for the removal of the first two numbers then cut and paste on the column I want to adjust? If so, can you tell me specifically how to do this and why I am this specific formula to achieve this action....... Thank you! |
#4
|
|||
|
|||
delete numbers in each cell in same column
So if I type/cut & paste this formula into B1 and then "copy down as
required" (how do I achieve the "copy down as required?) this will remove the first two digits in each cell below B1 for the entire column? "Jacob Skaria" wrote: With your data in Col A try the below in B1 and copy down as required =MID(A1,3,LEN(A1))+0 If this post helps click Yes --------------- Jacob Skaria "Anna Huber" wrote: Is there an easy way to just delete the first two numbers in each cell all the way down the column? i.e. 123456789 to 3456789? Would I have to create a helper column; put in a formula for the removal of the first two numbers then cut and paste on the column I want to adjust? If so, can you tell me specifically how to do this and why I am this specific formula to achieve this action....... Thank you! |
#5
|
|||
|
|||
delete numbers in each cell in same column
If you have data in cell A1 as 123456789 copy paste the formula in B1 will
return 3456789. Once done you can ........ 1. Copy the cell B1 (not the formula). Select the range down. Keeping the selection Right clickPaste SpecialFormulasOK OR 2. Select B1. Click the selection handle. Drag as required. OR 3. Select B1. Copy cell. Select the range down. Press Enter If this post helps click Yes --------------- Jacob Skaria "Anna Huber" wrote: So if I type/cut & paste this formula into B1 and then "copy down as required" (how do I achieve the "copy down as required?) this will remove the first two digits in each cell below B1 for the entire column? "Jacob Skaria" wrote: With your data in Col A try the below in B1 and copy down as required =MID(A1,3,LEN(A1))+0 If this post helps click Yes --------------- Jacob Skaria "Anna Huber" wrote: Is there an easy way to just delete the first two numbers in each cell all the way down the column? i.e. 123456789 to 3456789? Would I have to create a helper column; put in a formula for the removal of the first two numbers then cut and paste on the column I want to adjust? If so, can you tell me specifically how to do this and why I am this specific formula to achieve this action....... Thank you! |
#6
|
|||
|
|||
delete numbers in each cell in same column
Thank you for the quickk response. I need this in lay men's terms to
understand...step by step. If you are able to do so, I would greatly appreciate it! "Don Guillett" wrote: You could use a formula=RIGHT(A6,LEN(A6)-2) and copy down in a helper column. OR sub droptwo() mc="a" 'column A for i=1 to cells(rows.count,mc).end(xlup).row cells(i,mc).value=right(cells(i,mc),len(cells(i,mc )-2) next i end sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Anna Huber" Anna Huber @discussions.microsoft.com wrote in message ... Is there an easy way to just delete the first two numbers in each cell all the way down the column? i.e. 123456789 to 3456789? Would I have to create a helper column; put in a formula for the removal of the first two numbers then cut and paste on the column I want to adjust? If so, can you tell me specifically how to do this and why I am this specific formula to achieve this action....... Thank you! |
#8
|
|||
|
|||
delete numbers in each cell in same column
You can use TTC and not have to use formulas or helper columns.
Select the data in the column, then, from the Menu Bar, Data Text To Columns Fixed Width Next, In the second step of the Wizard, click in the "Data Preview" window to place the break line between the 2nd and 3rd digits. Then Next In the 3rd step of the Wizard, you'll see that the first 2 numbers are selected (colored black), and in their own column. NOW, click on "Do Not Import - Skip", which will skip that selected column containing the first 2 numbers. Finally click Finish, and you're done. Your numbers, minus the first two digits, are all that remain in the Column. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Anna Huber" Anna Huber @discussions.microsoft.com wrote in message ... Is there an easy way to just delete the first two numbers in each cell all the way down the column? i.e. 123456789 to 3456789? Would I have to create a helper column; put in a formula for the removal of the first two numbers then cut and paste on the column I want to adjust? If so, can you tell me specifically how to do this and why I am this specific formula to achieve this action....... Thank you! |
Thread Tools | |
Display Modes | |
|
|