VBA - copy the first three characters from a string to another cell

Question

I struggle with splitting the first three characters from a cell and paste them into another cell as a separate line.

My (example) input:

A A123456 A133457 B123456 B133457 ... 

What I want:

 AB A123456 A12 A133457 A13 B123456 B12 B133457 B13 ... ... 

What I tried:

 Dim ws As Worksheet Dim cell As Range Set ws = Worksheets("summary") For Each cell In ws.Range("A").Cells cell.Value = Left(cell.Value, 3) Next cell 

This will not work as I have overwritten the value in all A cells that I do not want. Can anyone help?

Answer 1

in Excel without VBA, you can use the formula: = LEFT (A1,3)

With VBA, you can do the following:

 For Each cell In ws.Range("A:A").Cells cell.Offset(0, 1).Value = Left(cell.Value, 3) Next cell 

Please note that I changed the range. You probably want to limit, since "A: A" will take some time.

The offset function says: "Use cell 1 to the right of the current cell."