Tags down


Delete text (left or right) with VBA so I only keep a specific string

By : Numismad
Date : October 18 2020, 06:10 AM
will be helpful for those in need Every day we get an Excel file which contains a database dump. Most of the file is being sorted in a VBA script that I made but I have one remaining problem that I'm unable to resolve. In one of the columns (A) I sometimes have 2 strings, I only like to keep one of those. The string I'd like to keep looks like "M1234 5678". The digits do change... The column sometimes has other text before or after the M1234 5678. I'd like to remove all that text so I only keep the needed string. , You could use a regex pattern if string length remains constant
code :
Option Explicit
Public Sub TrimText()
    Dim FinalValue As String, lastStop As Long, i As Long, re As Object
    Set re = CreateObject("VBScript.RegExp")
    With ThisWorkbook.Worksheets("Blad2")
        lastStop = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = 2 To lastStop
            FinalValue = Trim(Cells(i, 1).Value)
            .Cells(i, 1).Value = ReplaceMatch(re, .Cells(i, 1).Value, "M\d{4}\s\d{4}")
    End With
End Sub

Public Function ReplaceMatch(ByVal re As Object, inputString As String, ByVal pattern As String) As String
    With re
        .Global = True
        .MultiLine = True
        .pattern = pattern

        If .test(inputString) Then
            ReplaceMatch = .Execute(inputString)(0)
            ReplaceMatch = inputString
        End If
    End With
End Function

Share : facebook icon twitter icon

How to delete a specific string in a text file?

By : Andrew
Date : March 29 2020, 07:55 AM
This might help you How can I delete a specific string in a text file? , Locate the file.
code :
File file = new File("/path/to/file.txt");
File temp = File.createTempFile("file", ".txt", file.getParentFile());
String charset = "UTF-8";
String delete = "foo";
BufferedReader reader = new BufferedReader(new InputStreamReader(new FileInputStream(file), charset));
PrintWriter writer = new PrintWriter(new OutputStreamWriter(new FileOutputStream(temp), charset));
for (String line; (line = reader.readLine()) != null;) {
    // ...
    line = line.replace(delete, "");

Javascript / REGEX: Delete a specific Text (word) starting with a specific letter inside a String with words separated b

By : jlan50
Date : March 29 2020, 07:55 AM
I wish did fix the issue. I know this can quickly be done with Regex: , How about:
code :
NODE                     EXPLANATION
  \b                       the boundary between a word char (\w) and
                           something that is not a word char
  s                        's'
  \S+                      non-whitespace (all but \n, \r, \t, \f,
                           and " ") (1 or more times (matching the
                           most amount possible))

i is for case-insensitive
g is for global

Delete Everything(the rest of the text) after a specific string

By : Aurélie Perrin
Date : March 29 2020, 07:55 AM
help you fix your problem Using find and replace: Hit CTRL-H to open the Replace dialogue box enter gnirts.* into Find what leave Replace with empty Select Regular expression and .matches newline Click on Replace

In R, how do I wrap text around all words in a string, but a specific one(going from left to right)? Iteration and strin

By : Mhrl
Date : March 29 2020, 07:55 AM
help you fix your problem I know my question is a little vague, so I have an example of what I'm trying to do. , this is some butcher work but it gives the expected output :):
code :
input <- c('I go to school')
inp          <- c(list(NULL),strsplit(input," ")[[1]])
phantomize <- function(x,leftside = T){
 if(length(x)==1) return("")
   ph <- paste0('phantom("',paste(x[-1],collapse=" "),' ") * ') else
   ph <- paste0(' * phantom("',paste(x[-1],collapse=" "),'")')

# [[1]]
# [1] "I * phantom(\"go to school\")"
# [[2]]
# [1] "phantom(\"I \") * go * phantom(\"to school\")"
# [[3]]
# [1] "phantom(\"I go \") * to * phantom(\"school\")"
# [[4]]
# [1] "phantom(\"I go to \") * school"

Delete slides that contain a specific text string

By : mare
Date : March 29 2020, 07:55 AM
seems to work fine How about this modification?
Modification points : The reason that the entire slides are deleted is ; after if (currentPresentationSlide[i].getPageElements().indexOf('{{remove-this-slide}}') > -1);. By this ;, if doesn't work and currentPresentationSlide[i].remove(); is always run. The text data cannot be retrieved from currentPresentationSlide[i].getPageElements(). When you want to search the text from the text box, please use currentPresentationSlide[i].getShapes(). From your question, I was not sure where you want to search the text from. So I supposed that you want to search the text from shapes. The shape includes the text box. Modified script :
code :
function deleteFunction() {
  var currentPresentationSlide = SlidesApp.getActivePresentation().getSlides();
  for (i = 0; i < currentPresentationSlide.length; i++) {
    var shapes = currentPresentationSlide[i].getShapes();
    for (j = 0; j < shapes.length; j++) {
      if (shapes[j].getText().asString().indexOf('{{remove-this-slide}}') > -1) {
Related Posts Related Posts :
  • How can I clear the contents of (only) hidden cells in Excel (and not delete them)?
  • Using Application.OnKey to Disable CTRL-V
  • Not able to find unique values in excel
  • Excel stealing keyboard focus from VCL Form (in AddIn)
  • Multiplying an amount by 0.45 up until 833 then the remain by 0.25
  • Sort multiple rows Left-to-Right
  • Sorting data in columns with variable number of columns
  • Speeding up macro that goes through a workbook overwriting sheets by value
  • How can I optimize this macro that sorts and formats SQL data?
  • Copy a cell to a merged cell while keeping hyperlink value
  • Multiple IF arguments layered between cells
  • 432 4.3.2 STOREDRV.ClientSubmit; sender thread limit exceeded?
  • How to convert unusual datetime formats in excel to year, month, day, and time cells respectively?
  • Flashing animation getting interrupted by DoEvents
  • Delete multiple Excel Sheets in VBA
  • I Bad: VBA Confusion
  • Add spaces to a string in excel vba, later used in .HTMLBody
  • Using While loop for getting the handle of rows in a particular Column
  • How to perform SUM of multiple VLOOKUP values by treating N/A values as Zero?
  • FIND function in VBA - Object does not support this property or Method
  • VBA Excel: “Automation Error. Exception occurred.” when using UserForm
  • Find specific lower or uppercase
  • group the results of a matching function
  • "Standard Deviation If" in VBA
  • How to use Conditional Formatting?
  • Match Function Shows Incorrect/Repeat Position
  • Add character to the beginning of a cell that start with number
  • How can I handle DLL errors in VBA?
  • Copy a Hidden Excel Sheet
  • How to import a table from web page (with "div class") to excel?
  • format excel file after export-csv powershell
  • Cancel = True is not working in Workbook_BeforeClose
  • Is there a way to retrieve information from a text file in vba?
  • Comparing two columns and if it matches then it should be highlighted in another column
  • How to get temperature of datetime value based on the table which contains day and night wise temperature data
  • How to Define a Pivot Cache?
  • Named argument not found vba compile error
  • Calling multiple Sub's with one line
  • Excel VBA Dictionary Data Integrity Issue
  • Copying a column in excel and doubling every entry/cell. How to?
  • How to continue running a VBA macro when an SAP popup message tells us that there is no data found?
  • Define List that Changes
  • How do I search a list of characters in a text
  • Clear cell content based on another cell for each row
  • Deleting a part of a cell
  • Skip over function error and continue with next check (VLOOKUP)
  • How to fix "value used in formula is wrong data type" error when trying to convert a range into an array?
  • Reverse order of days auto-populated by macro that creates new sheet named 06/01, 06/02 and so on
  • Formula help for multiple conditions in excel conditional fomatting
  • Not in search string - It works when it is
  • Pasting values from one Excel Workbook to others
  • Excel VBA open folder and get GPS info (Exif) of each files in it (2)
  • Runtime Error 5: Removing Certain Items from VBA Collection
  • Why is this VBA function slow?
  • How to return worksheet or workbook name in cell
  • VBA change font properties of chart axes without using With-EndWith Statement
  • Find a string within an Excel file and get the row index
  • Referencing Public Constants in Cell Formulas
  • How to make "edit links" VBA code run faster?
  • shadow
    Privacy Policy - Terms - Contact Us © 35dp-dentalpractice.co.uk