Changing the value of a column of cells

  • Thread starter Thread starter Parasyke
  • Start date Start date
P

Parasyke

Does anyone know how to change a section of a column of cells with this
scenario?:
I have about 3500 rows in this column with this as part of the present
values(all are text, not numeric):

103 which I want changed to: Part # 103
104 which I want changed to: Part # 104
105 which I want changed to: Part # 105
106 which I want changed to: Part # 106
107 which I want changed to: Part # 107
108 which I want changed to: Part # 108
109 which I want changed to: Part # 109
110 which I want changed to: Part # 110
111 which I want changed to: Part # 111
112 which I want changed to: Part # 112
etc.
Needles to say I don't want to do this manually!

Thanks for anyone's help! Dav
 
Say you have your numbers in Col A, starting in Row 2, with no blank rows in
you data range. Insert a new Column B. Type the following formula in B2:
="Part # "&A2
Move your cursor to the right lower corner of B2, double click your mouse to
copy down to the last row. Press <Ctrl><C>, go to Cell A2, right click and
select Paste Special. Select Values and OK out. Delete Column B and you're
done
 
As long as there are no empty cells within the range this little macro
works:

Sub AddPart()
Dim c As Range
For Each c In Selection
c.Value = "Part # " & c.Value
Next c
End Sub

Place it in a general module. Select the range of cells you want to change
then run the macro.

Biff
 
Hi Parasyke,

Probably the easiest way is to select the range you want to update and run a
macro like the following:

Sub AddPrefix()
Dim oRange As Range
Dim oCell As Range
If Selection.Columns.Count * Selection.Rows.Count = 1 Then
Set oRange = ActiveCell
Else
Set oRange = Selection.SpecialCells(xlConstants)
End If
For Each oCell In oRange
oCell.Value = "Part # " & oCell.Value
Next oCell
End Sub

If this is the first time you've encountered macros, see the article "What
do I do with macros sent to me by other newsgroup readers to help me out?"
at:
http://www.word.mvps.org/FAQs/MacrosVBA/CreateAMacro.htm


Cheers
 
Enter this command in the immediate window and press enter:

[a2:a3501]=["Part # "&a2:a3501]

or adapt for the required range.

(Note: Press Alt+F11 Ctrl+G to activate immediate window.)
 
Back
Top