CODEWITHSUNDEEP
vbaexcel
MrPatterns
MrPatterns

Reputation: 4434

How do I remove a character from a column of data in Excel

I am formatting some data I received. I have several hundred names of students in Column A, and for some strange reason there is a random * placed randomly throughout the names. I want to programmatically remove all * characters from all names.

 For x = 2 To 300

        Dim strStudent as String

        //how do i set contents of cell to string strStudent

        strStudent = Replace(strStudent, "*", "") //replace * with nothing

 Next

My question is, how do I set the contents of the cell to a strStudent? Then I can test if I'm doing the replace part right.

Upvotes: 2

Views: 7653

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149295

You don't need to loop through the cell. You can use Excel's inbuilt .Replace function to replace all * by using "~*"

Here is an example

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim Rng As Range

    '~~> Change this to the relevant worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    '~~> Change this to the relevant range
    Set Rng = ws.Range("A2:A300")

    Rng.Replace What:="~*", Replacement:="", LookAt:=xlPart, SearchOrder _
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

Upvotes: 4

Related Questions