• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pivot help

J

Jagdev Singh

Active Member
Hi Experts,

I have a pivot table and I want the remove the words mentioned like (Reublic of korea) etc in the row labels list of the pivot

Ex

ABC Konnai, Inc.
ABC KOnnai, Inc. (Republic Of Korea)
Jagdev Singh
Jagdev Singh (Turkey)

I want to remove such occurrences from the list throughout. I am aware of label filters. Is it something which can be helpful here?

Regards,
JD
 
Hi Bobhc

I have such data in 100s of count. The above solution will work if the count of such entries is fewer. Is there any other way to remove the braces with data init from the entire row label list.

Regards
JD
 
As per my above sample the outcome after the removal should be like

ABC Konnai, Inc.
ABC KOnnai, Inc.
Jagdev Singh
Jagdev Singh

Regards,
JD
 
Hi JD,

I think you have to add a helper column in your base data with some formula to tackle it indirectly in Pivot. Can you provide a sample file with raw data and a pivot out of it, may be 100 lines from the data with various variations.

Regards,
 
Hi Somendra

Could you please help me out with the macro which will remove () in the RAW data itself. In the attached same from column "A" wherever it will find () it should remove it.

Regards,
JD
 
Jagdev Singh said:
Hi Somendra

Could you please help me out with the macro which will remove () in the RAW data itself. In the attached same from column "A" wherever it will find () it should remove it.

Regards,
JD
Click to expand...


Try below code:

Code: 
Option Explicit

Sub cleanData()

Dim ws As Worksheet
Dim lastRow As Long
Dim cell As Range
Dim rng As Range

Set ws = ThisWorkbook.Worksheets("Sheet1")
lastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = ws.Range("A2:A" & lastRow)

For Each cell In rng
    If InStr(1, cell, "(", vbTextCompare) > 1 Then
        cell.Value = Mid(cell.Value, 1, InStr(1, cell.Value, "(", vbTextCompare) - 2)
    End If
Next

End Sub
 
You must log in or register to reply here.
Back
Top