Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing the value of a column of cells
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing the value of a column of cells
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 "Parasyke" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing the value of a column of cells
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 "Parasyke" wrote in message oups.com... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing the value of a column of cells
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/Macros...eateAMacro.htm Cheers -- macropod [MVP - Microsoft Word] "Parasyke" wrote in message oups.com... 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing the value of a column of cells
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.) Parasyke wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup function/sum function | Excel Discussion (Misc queries) | |||
macro | Excel Discussion (Misc queries) | |||
How do I make 2 cells become one without changing column size | Excel Discussion (Misc queries) | |||
Resize individual cells w/o changing the entire column width | Excel Discussion (Misc queries) | |||
COUNT NON-BLANK CELLS WITH REFERENCE TO ANOTHER COLUMN | Excel Worksheet Functions |