CODEWITHSUNDEEP
excelstring-formattingvba
MadChadders
MadChadders

Reputation: 127

Using Left without Copy & Paste

I have some data that I want to be basically cut down to the first 12 numbers no matter how many it goes out. The way I'm currently doing it is taking the numbers from the column C and in Column G I have a formula =Left(C3,12) I then drag down so it matches the range in C. Then I run a macro that looks like this:

Sub Macro6()

    Range("G3:G7").Select
    Selection.Copy
    Range("C3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

 End Sub

This works but I have to constantly update the range, and it's kind of clunky. Does anyone know a better way to accomplish the same thing? (basically making the values in C become the first 12 digits only)

Upvotes: 1

Views: 259

Answers (3)

Automate This
Automate This

Reputation: 31364

If I understand you correctly, your wanting to replace the contents of a cell in column C with the first 12 digits of whatever is in that same cell.

Starting with data that looks like this:

enter image description here

Run This:

Sub keep12()
    Dim wks As Worksheet
    Set wks = Worksheets("Sheet1")

    Dim rng As Range
    Set rng = wks.Range("C3:C" & wks.Range("C" & wks.Cells.Rows.Count).End(xlUp).Row)

    For Each cell In rng
        cell.Value = Left(cell.Text, 12)
    Next cell
End Sub

Result:

enter image description here

Upvotes: 1

peege
peege

Reputation: 2477

The other answers are valid solutions for this as well, with the Excel formula answer being the most efficient way to accomplish it @Siddharth Rout. I am simply adding another method, that in this case might more directly answer the OP's specific needs, to copy the data from Column C to Column G.

Sub LeftFilterAll()

Dim sheetName As String
Dim LastRow As Long

sheetName = "Sheet1"
LastRow = Sheets(sheetName).Range("C" & Rows.Count).End(xlUp).row   ' Count the last row in Column C

For row = 2 To LastRow
 'Left 12 From C to G
    Sheets(sheetName).Cells(row, 7).Value = Left(Sheets(sheetName).Cells(row, 3).Text, 12)
Next row

End Sub

Down the road, you might want to make it so it simply UPDATES the values in Column G, and doesn't replace them all, if you find you are running this over and over for 1 or two new records, instead of the whole group, you get the last row of column G and start from that row number. So you'd have the following only updating. This is no good if you plan on changing values in C after they are entered, as they'd be processed only once. 

Sub LeftFilterUpdate()

Dim sheetName As String
LastCRow = Sheets(sheetName).Range("C" & Rows.Count).End(xlUp).row   ' Count the last row in Column C
LastGRow = Sheets(sheetName).Range("G" & Rows.Count).End(xlUp).row   ' Count the last row in Column G

For row = LastGRow To LastCRow
 'Left 12 From C to G
    Sheets(sheetName).Cells(row, 7).Value = Left(Sheets(sheetName).Cells(row, 3).Text, 12)
Next row

End Sub

Upvotes: 1

Siddharth Rout
Siddharth Rout

Reputation: 149305

Fastest way! No VBA required.

  1. Insert a blank column after Col A.
  2. Highlight Col A.
  3. Click on Data | text To Columns
  4. Select Fixed Width
  5. Set your field with as shown in the image after 12 characters and click on Finish
  6. Delete column B (The one we inserted.)

enter image description here

And here is the output

enter image description here

Upvotes: 2

Related Questions