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