Editing multiple cells at once

[DMT]

Here's the problem. Say I have a column of 5 numbers. They are 1000, 2000, 3000, 4000, and 5000. I want to convert these to thousands, but I don't want to make another column with a formula. I want to change those existing cells. So, basically, I would like to include /1000 to each of those cells, without going into each cell and entering the formula. Since all the cells will be edited in the same way, I'm guessing there is an easy way to apply the same change to all selected cells. Can I do this? Thanks for the help.

 

[Joe4]

Simply highlight the range you want to fix and run this simple macro:

Sub FixNumbers()

    Application.ScreenUpdating = False
    
    Dim cell As Range
    For Each cell In Selection
        cell = cell / 1000
    Next cell
    
    Application.ScreenUpdating = True
    
End Sub

 

[Seti]

You could also put 1000 in a blank cell, copy this cell, then PasteSpecial and under the Operation section choose divide. If the cells contains values, then they will be divided by 1000 and if they contain formulas, the formulas will be adjusted to show division by 1000.

 

[DMT]

Thanks,

Is there a way to do this without a macro? I seem to remember someone showing me a way to do it just using a certain key combination.

 

[Joe4]

See Seti's reply above for a macro-less solution.

 

[Manpower]

I have a similar questions, but I do not want to replace any of the data.

I have data that is exported to excel that look like dates but are text instead:

Fig.1
<TABLE style="WIDTH: 98pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=130 border=0><COLGROUP><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4754" width=130><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5 0.5pt solid; WIDTH: 98pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=130 height=20>11-JUL-2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5 0.5pt solid; BORDER-BOTTOM: #d0d7e5 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>02-MAY-2010</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5 0.5pt solid; BORDER-BOTTOM: #d0d7e5 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>24-APR-2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5 0.5pt solid; BORDER-BOTTOM: #d0d7e5 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>04-MAY-2011</TD></TR></TBODY></TABLE>
​

After formatting the cells to date and specify the format to: dd-mmm-yy, it still looks like the above. When I click on the cell, hit F2 and enter, it turns the cells to the date format:

Fig. 2
<TABLE style="WIDTH: 98pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=130 border=0><COLGROUP><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4754" width=130><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl72 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5 0.5pt solid; WIDTH: 98pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right width=130 height=20>11-Jul-11</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl72 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5 0.5pt solid; BORDER-BOTTOM: #d0d7e5 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>2-May-10</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl72 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5 0.5pt solid; BORDER-BOTTOM: #d0d7e5 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>24-Apr-11</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl72 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5 0.5pt solid; BORDER-BOTTOM: #d0d7e5 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>4-May-11</TD></TR></TBODY></TABLE>

​

Fig. 2 is what I want. I have over 1000 cells that needs to be converted, using the F2-Enter function. Is there way to edit and enter all cells at once, or create a macro to do it, without replacing the data within the cell?

The reason for needing to edit all the cells with Fig.1 to Fig.2, is because I have date formulas and it wouldn't recognize the text as dates, until it is converted manually by F2-Enter.

 

[shg]

Select the column, Data > Text to columns, Finish.

 

[Manpower]

Awesome! That works--and soo easy. Thank you for the quick reply, shg4421

 

[IJ_Alducente]

TE AMO!
Thank you!