PDA

View Full Version : Abstract first 6 characters from a column



Mel_P
07-02-2007, 01:18 PM
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
07-02-2007, 01:23 PM
For n = 2 to (Number of rows)
Cells(i,"B").Value = Left$(Cells(i,"C").Value,6)
Next n

Mel_P
07-02-2007, 01:47 PM
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
07-02-2007, 02:06 PM
Yes, i should be n

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

mikerickson
07-02-2007, 02:54 PM
The spreadsheet forumula =LEFT(C2,6), placed in B2 and copied down sounds easier.

Bob Phillips
07-02-2007, 03:01 PM
Except that the OP said they wanted to do it in VBA.

johnske
07-02-2007, 04:30 PM
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.
With Range("C2", Range("C" & Rows.Count).End(xlUp)).Offset(0, -1)
.Formula = "=Left(C2,6)"
.Value = .Value
End With

unmarkedhelicopter
07-03-2007, 01:15 AM
Surely an Abstract of the first 6 characters would be @@@@@@ ;)

Mel_P
07-05-2007, 02:42 PM
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