CODEWITHSUNDEEP
vbaexcel
user1265125
user1265125

Reputation: 2656

Changing string in cells

I have cells with data like these:

ABCD123XYZ  
MPOP345MLU
.  
.  
.  
.

What I want to do is, to remove the 3rd and 4th character from all these cells, hence giving

AB123XYZ  
MP345MLU

How do I do this in VBA?

I have no idea about this language, and if someone can guide me in the right direction, it'd be great :)

I understand I have to:

  1. Declare 2 string variables st1 and st2
  2. Store data from cell A1 into the variable st1
  3. Copy all but the 2nd and 3rd char into str2
  4. Output Str2 into B1
  5. Move to A2

Upvotes: 1

Views: 179

Answers (4)

jdl
jdl

Reputation: 6323

use the "mid" function 

Dim str1 As String
Dim str2 As String

str1 = Cells(1, 1) '"ABCD123XYZ"
str2 = Cells(2, 1) '"MPOP345MLU"

Cells(1, 2) = Mid(str1, 1, 2) & Mid(str1, 5) 'yield AB123XYZ
Cells(2, 2) = Mid(str2, 1, 2) & Mid(str2, 5) 'yield MP345MLU

Upvotes: 1

mechanical_meat
mechanical_meat

Reputation: 169274

@DougGlancy's got your answer already,
but if you really want to use VBA: 

Sub substr_example()
  ThisWorkbook.Sheets(1).Range("B1:B100").FormulaR1C1 = _
      "=LEFT(RC[-1],2)&MID(RC[-1],5,LEN(RC[-1])-4)"
End Sub

Upvotes: 1

Siddharth Rout
Siddharth Rout

Reputation: 149287

Another way using Replace() formula where A1 is the cell which has the text. You can simply copy the formula down.

=REPLACE(REPLACE(A1,3,1,""),3,1,"")

VBA CODE

Sub Sample()
    Dim LastRow As Long

    With Sheets("Sheet1")
        LastRow = .Range("A" & Rows.Count).End(xlUp).Row

        .Range("B1").FormulaR1C1 = "=REPLACE(REPLACE(RC[-1],3,1,""""),3,1,"""")"
        .Range("B1").AutoFill Destination:=Range("B1:B" & LastRow), Type:=xlFillDefault
    End With
End Sub

Upvotes: 3

Doug Glancy
Doug Glancy

Reputation: 27478

You don't need VBA for this. Just use a formula:

=LEFT(A1,2) & MID(A1,5,9999)

Upvotes: 1

Related Questions