Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying first 4 characters of column
I want to write a macro for copying first number of letters of a value of the cell. for eg. My column contains values as abcdefghijklmnop xyzsgfht reenap pppppppppppp Now here I want to select only "abcd" - first 4 characters of the entire column and copy it in other excel sheet. New sheet should contain abcd xyzs reen pppp as column. How can I do that? -- reena ------------------------------------------------------------------------ reena's Profile: http://www.excelforum.com/member.php...o&userid=30440 View this thread: http://www.excelforum.com/showthread...hreadid=506265 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying first 4 characters of column
maybe something like this, just change the references to your needs
Option Explicit Sub move_data() Dim lastrow As Long Dim cell As Range Dim i As Integer i = 1 lastrow = Worksheets("Sheet1").Cells(Rows.Count, "A") _ ..End(xlUp).Row With Worksheets("Sheet2") For Each cell In Range("A1:A" & lastrow) ..Range("A" & i).Value = Left(cell, 4) i = i + 1 Next End With End Sub -- Gary "reena" wrote in message ... I want to write a macro for copying first number of letters of a value of the cell. for eg. My column contains values as abcdefghijklmnop xyzsgfht reenap pppppppppppp Now here I want to select only "abcd" - first 4 characters of the entire column and copy it in other excel sheet. New sheet should contain abcd xyzs reen pppp as column. How can I do that? -- reena ------------------------------------------------------------------------ reena's Profile: http://www.excelforum.com/member.php...o&userid=30440 View this thread: http://www.excelforum.com/showthread...hreadid=506265 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying first 4 characters of column
Option Explicit
Sub Copy4Letters() 'copies sheet1!A1... 'to sheet2!A1.. ' but only first 4 letters Dim lastrow As Long lastrow = Worksheets("Sheet1").Range("A1").End(xlDown).Row With Worksheets("Sheet2") With .Range(.Range("A1"), .Cells(lastrow, 1)) .Formula = "=LEFT(Sheet1!A1,4)" .Calculate .Value = .Value End With End With End Sub "reena" wrote: I want to write a macro for copying first number of letters of a value of the cell. for eg. My column contains values as abcdefghijklmnop xyzsgfht reenap pppppppppppp Now here I want to select only "abcd" - first 4 characters of the entire column and copy it in other excel sheet. New sheet should contain abcd xyzs reen pppp as column. How can I do that? -- reena ------------------------------------------------------------------------ reena's Profile: http://www.excelforum.com/member.php...o&userid=30440 View this thread: http://www.excelforum.com/showthread...hreadid=506265 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying first 4 characters of column
I would cheat a bit and use Excels built in Text to Columns function.
Record yourself copying the column, pasting it elsewhere & then doing text to columns, with a fixed width of 4, then deleting the column of unwanted letters. If all your data was in column K it would look like this Sub First4letters Columns("K:K").Select Selection.Copy Workbooks.Add Columns("A:A").Select ActiveSheet.Paste Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _ FieldInfo:=Array(Array(0, 1), Array(4, 1)) Columns("B:B").Select Selection.ClearContents End Sub The FieldInfo attribute of the text to Columns function is where the number of letter you'd like to include is set. The second array has (4,1), this is where VBA tells excel that all letters after the first 4 go into the second column. Hope this helps "reena" wrote: I want to write a macro for copying first number of letters of a value of the cell. for eg. My column contains values as abcdefghijklmnop xyzsgfht reenap pppppppppppp Now here I want to select only "abcd" - first 4 characters of the entire column and copy it in other excel sheet. New sheet should contain abcd xyzs reen pppp as column. How can I do that? -- reena ------------------------------------------------------------------------ reena's Profile: http://www.excelforum.com/member.php...o&userid=30440 View this thread: http://www.excelforum.com/showthread...hreadid=506265 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying first three characters of text in a cell | Excel Worksheet Functions | |||
How do I get the last 7 characters of a no. to another column? | Excel Worksheet Functions | |||
Copying over only limited number of characters | Excel Discussion (Misc queries) | |||
How do I cut column back to first 2 characters? Thanks | Excel Discussion (Misc queries) | |||
Copying a formula in a blank column as far as data in previous column | Excel Programming |