CODEWITHSUNDEEP
vbaexcel
sam
sam

Reputation: 10094

delete data in cell after specific character

I have data in cells A1:A1000. It is a list of names followed by a small note, like this:

They all have " and a space after the names and then the note. What I am trying to do is delete the everything after the name.

I was playing around with macros to try and do this, but could not get anything to work. Any idea how I might do this?

Upvotes: 10

Views: 93617

Answers (6)

mohitgupta
mohitgupta

Reputation: 1

Press ctrl + f, click on replace tab, type * in the find what box and then click on replace all. No need to put anything in replace box. Here you are replacing everything after ..

Upvotes: 0

ApplePie
ApplePie

Reputation: 8942

Here is a nifty trick without macros:

Select the proper range (or even just click on A to select the entire column) and then do Ctrl+F, click Replace, in Find write exactly "* and leave the Replace with box empty. Now click Replace all and tada !

It replaces everything after (and including) the quote with nothing because it uses * as a wildcard you left the replace box empty.

Edit: As suggested here is the VBA code for this:

Columns("A:A").Replace What:="""*", Replacement:="", LookAt:=xlPart

Upvotes: 26

Peter Albert
Peter Albert

Reputation: 17515

In case you want to keep your source data, you can also do it with a simple Excel formula in the next column. Assuming that your data is in column A, the following formula will return only the name: =LEFT(A1,SEARCH("""",A1)-1)

Upvotes: 2

InContext
InContext

Reputation: 2501

few options:

Replace

Range("A1:A1000").Replace """*", vbNullString

If you require to manipulate the value further then the below are more appropriate:

With Regex:

Dim str As String, strClean As String
Dim cell As Range

For Each cell In Range("A1:A1000")

    With CreateObject("vbscript.regexp")
        .Pattern = "\""(.*)"
        .Global = True
        cell = .Replace(cell, vbNullString)
    End With

Next cell

Without Regex, splitting the string:

Dim strSplit() As String
Dim cell As Range

For Each cell In Range("A1:A1000")

    If (cell.Value <> vbNullString) Then

        cell.Value = Split(cell.Value, """")(0)

    End If

Next cell

Upvotes: 4

john_science
john_science

Reputation: 6571

Easy! I don't know what version of Excel you are using, but in short you want to do a Convert Text to Columns and then split the cells using a delimiter of ". This will leave you with two columns, one of the data you want and one you can just delete.

Here is the walk through in Office 2010:

  1. Highlight column A
  2. find the Data menu
  3. find the Convert Text to Columns menu
  4. Pick Delimited and hit next
  5. In the Other box, type "
  6. hit Finish

Done! Now you have all your names in column A and you can just delete column B.

To sum up, do a "Convert Text to Columns" and then split the cells using a delimiter of ". Super easy and fast.

Upvotes: 5

Ernie
Ernie

Reputation: 86

Sub Macro1()
    For Row = 1 To 1000
       S = Range("A" & Row).Cells.Value
       Pos = InStr(S, Chr(34))
       If Pos > 0 Then Range("A" & Row).Cells.Value = Left(S, Pos - 1)
    Next
End Sub

Upvotes: 1

Related Questions