#1
|
|||
|
|||
Text removal
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 |
#2
|
||||
|
||||
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
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#3
|
|||
|
|||
RunTime Error 5991
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. |
#4
|
|||
|
|||
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... |
#5
|
||||
|
||||
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
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#6
|
|||
|
|||
@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... |
#7
|
|||
|
|||
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 Last edited by ranjan; 08-11-2023 at 11:21 AM. Reason: Added Code |
#8
|
||||
|
||||
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
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
|
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 |