Text removal

ranjan · #1 03-16-2022, 05:08 AM

I have a document with a dual presentation Native and English, the presentation is like “NATIVE TEXT/ENGLISH TEXT”, and I need to remove only the native text before the backslash.

If the text before the backslash is anything other than English or Numbers It should get deleted along with the backslash.

Can Macro/Wdreplace be used to remove the text before the back slash? If so, could you please assist me in this regard.

Although I'm not quite sure if we could automate this work or not, if it doesn't seem realistic, please excuse me…

In a table, each line item containing a dual language is presented in a separate cells, and text before the backslash to the begging point of the cell should be deleted if the text is other than English and numbers, please see the below example.

I really appreciate your help and my sincere gratitude goes out to you for your support.

Input:

Harga Perolehan/ Acquisition Cost
Nilai Aset Neto/ Net Asset Value
Penurunan Nilai Goodwill/ Goodwill Impairment
Goodwill Neto/ Goodwill Net
Income/Loss
234/989

Output:
Acquisition Cost
Net Asset Value
Goodwill Impairment
Goodwill Net
Income/Loss
234/989

gmayor · #2 03-16-2022, 05:43 AM

They following, based on your example, will work.

Code:

Sub Macro1()
Dim oTable As Table
Dim i As Long
Dim oRng As Range
Dim sText As String
    Set oTable = Selection.Tables(1)
    With oTable
        For i = 1 To .Rows.Count
            Set oRng = .Rows(i).Cells(1).Range
            If InStr(1, oRng.Text, "/") > 0 Then
                sText = Split(oRng.Text, "/")(0)
                If Not sText = "Income" And IsNumeric(sText) = False Then
                    oRng.Text = Trim(Split(oRng.Text, "/")(1))
                End If
            End If
        Next i
    End With
    Set oTable = Nothing
    Set oRng = Nothing
End Sub

ranjan · #3 03-16-2022, 07:50 AM

Hi,

Thanks for your prompt response, its quite working well on individual cells, while running on table its getting error "5991" after deleting a native text its creating a table enter for every cell. I had given example as Income but here condition is .([a-z]) i.e. english words or terms shouldnt get deleted before the backslash.
Example: long/short , assets/debt, equity/sales, these are all examples and shouldnt get affected after running a macro.
Likewise values before the backslash also shoulldnt have impact.
Example: 1203/34343 , 1234/marks, 2021/stats

Issues:

Not running on a table giving error as 5991.

English words and values remain constant before the backslash.

Native text (Otherthan English Words (A-Z) & Values (0-9) only should deleted before the backslash including "/" (Strarts from begining of the cell to "/" get deleted).

Table Enter was creating deleting a native text in a individual cells. Table Enter again involves manual work to remove extra table enter.

Please find a attachment.

Your Help is highly appreciated.

ranjan · #4 03-23-2022, 07:25 AM

HI,

DOES THE CODE HAVE A POSSIBILITY OF BEING REVIEWED AND MADE WORKABLE AS PER MY REQUIREMENTS?

I really appreciate your help and efforts....

Thanks a lot...

gmayor · #5 03-23-2022, 10:05 PM

There is no way to determine whether the words you want to keep are different from the words you want to discard, when both are formatted as US English, as in your example. The only practical solution I can think of is to check against a predefined list, which is the solution adopted below. The error relates to the fact that your current example document has merged cells, which are more difficult to work with. However the following works with your example document. You can add words and/or phrases to the array if your tables have different combinations of strings in the cells.

Code:

Sub Macro1()
Dim vList As Variant
Dim oTable As Table
Dim oCell As Cell
Dim i As Long
Dim oRng As Range
Dim sText As String
Dim bOmit As Boolean

    vList = Array("Goodwill", "Income", "Net Cost", "A") 'The words to ignore when they appear to the left of the '/' character.

    Set oTable = Selection.Tables(1)
    With oTable
        For Each oCell In oTable.Range.Cells
            Set oRng = oCell.Range
            oRng.Case = wdTitleWord
            If InStr(1, oRng.Text, "/") > 0 Then
                sText = Split(oRng.Text, "/")(0)
                If IsNumeric(sText) = False Then
                    bOmit = False
                    For i = 0 To UBound(vList)
                        If CStr(vList(i)) = sText Then
                            bOmit = True
                            Exit For
                        End If
                    Next i
                    If bOmit = False Then
                        oRng.Text = Trim(Split(oRng.Text, "/")(1))
                    End If
                End If
            End If
        Next oCell
    End With
    Set oTable = Nothing
    Set oRng = Nothing
End Sub

ranjan · #6 03-24-2022, 12:40 AM

@Graham Mayor

You explained things better than I did and I appreciate how you responded.

It will be helpful to me and I will make a list of words and add them to the array.

It's always amazing how you put things into perspective and that you make the difficult things seem easy with a single click...

Tons of THANKS for your help...

ranjan · #7 08-11-2023, 09:25 AM

Hi,

The above code is fully operational; however, I would like to apply these macros to all tables in a document; can we update this code to apply to all tables in a document?
If feasible, modify the one above....

Your help is highly appreciated.

I had tried the below one.. but not working..

For Each tbl In ActiveDocument.Tables
For Each cell In tbl.Range.Cells
Set rng = cell.Range
rng.Start = rng.Start + InStr(1, rng.Text, "") - 1
rng.Delete
Next cell
Next tbl

gmayor · #8 08-12-2023, 08:54 AM

To run the last macro I posted on all tables just add the loop around the code e.g.

Code:

Sub Macro1()
Dim vList As Variant
Dim oTable As Table
Dim oCell As cell
Dim i As Long
Dim oRng As Range
Dim sText As String
Dim bOmit As Boolean

    vList = Array("Goodwill", "Income", "Net Cost", "A") 'The words to ignore when they appear to the left of the '/' character.

    For Each oTable In ActiveDocument.Tables
    With oTable
        For Each oCell In oTable.Range.Cells
            Set oRng = oCell.Range
            oRng.Case = wdTitleWord
            If InStr(1, oRng.Text, "/") > 0 Then
                sText = Split(oRng.Text, "/")(0)
                If IsNumeric(sText) = False Then
                    bOmit = False
                    For i = 0 To UBound(vList)
                        If CStr(vList(i)) = sText Then
                            bOmit = True
                            Exit For
                        End If
                    Next i
                    If bOmit = False Then
                        oRng.Text = Trim(Split(oRng.Text, "/")(1))
                    End If
                End If
            End If
        Next oCell
    End With
    Next oTable
    Set oTable = Nothing
    Set oRng = Nothing
End Sub

I am not sure what your example is supposed to achieve, so cannot establish whether it is working correctly or not.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Removal of resource calendars?	Snoboy	Project	0	10-07-2020 12:28 PM
pst password removal	darnero	Outlook	0	03-07-2018 07:24 AM
Removal first 7 typos	Chetan Seebra	Excel Programming	2	03-31-2015 12:57 PM
table removal syntax	EAGLE SEU	Word VBA	5	01-23-2015 10:57 AM
Field removal from template	Phelony	Word	1	10-18-2011 03:28 AM