Copying first 4 characters of column

  • Thread starter Thread starter reena
  • Start date Start date
R

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top