Copying first 4 characters of column

[reena]

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?

[Gary Keramidas]

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[Patrick Molloy]

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
>
>

[Hluhluwe]

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
>
>