Abstract first 6 characters from a column

[Mel_P]

Hello - I have a spreadsheet column (C) in which the first 6 characters are a code number and the rest is text. I want to extract them into a column (B) which I have created with VBA.

So what I want to do is

read cell C2 and extract 6 characters - Left (contents of C2,6)
put this in cell B2
move to row 3
read cell C3 and extract 6 characters - Left (contents of C3,6)
put this in cell B3

and so on until I reach the last row - I have found the number of rows used so my loop will be
For n = 2 to (Number of rows)
Code for above.
Next n

I can't get my head round excell syntax!

Can anybody advise please?

Thanks, Mel P

[Bob Phillips]

[vba]

For n = 2 to (Number of rows)
Cells(i,"B").Value = Left$(Cells(i,"C").Value,6)
Next n
[/vba]

[Mel_P]

Thanks - the i should be n?
Do i need the $ sign

This works:

Dim n As Integer
For n = 2 To Num_rows
Cells(n, "B").Value = Left(Cells(n, "C").Value, 6)
Next n

Thanks very much, Mel

[Bob Phillips]

Yes, i should be n

You don't need the $, but as it is a string function, I use the string ($) version.

[mikerickson]

The spreadsheet forumula =LEFT(C2,6), placed in B2 and copied down sounds easier.

[Bob Phillips]

Except that the OP said they wanted to do it in VBA.

[johnske]

Mel,

you want it in VBA, but looping is slow, so combining VBA with Excel formula is much faster for this type of problem. e.g.
[VBA] With Range("C2", Range("C" & Rows.Count).End(xlUp)).Offset(0, -1)
.Formula = "=Left(C2,6)"
.Value = .Value
End With[/VBA]

[unmarkedhelicopter]

Surely an Abstract of the first 6 characters would be @@@@@@

[Mel_P]

Ho Ho to last.

I will try the combination from Johnske - it's easy to forget the obvious!

Thanks to all.

My first use of this site and 100% satisfaction!

Mel