Click here to Skip to main content
15,039,359 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi all,

I have an excel workbook with lot of information. What I'm trying to do is to find string inside a cell, then deleting that string and next 5 number characters.

Between string and number chars it has one space. I can find string, but then I don't know how to delete that string and next 5 numbers. One important thing is that in one cell it can appear more than one same string format, so i can't delete all entire cell


String format is: Word 12345


thanks in advance! :)
Posted
Comments
Maciej Los 24-Oct-12 16:16pm
   
Please, provide more informations and example data.

1 solution

Try something like this:
VB
Option Explicit

'required reference to MS VBScript Regular Expression 5.5
Sub RemoveMatches()
Dim wsh As Worksheet, oRegEx As VBScript_RegExp_55.RegExp 
Dim i As Integer, sTmp As String, sPattern As String

On Error GoTo Err_RemoveMatches

Set wsh = ThisWorkbook.Worksheets(1)

sPattern = "\W*([a-zA-Z]{1,} [0-9]{5})"
i = 1
Do While wsh.Range("A" & i) <> ""
    sTmp = wsh.Range("A" & i)
    Set oRegEx = New VBScript_RegExp_55.RegExp
    With oRegEx
        .Pattern = sPattern
        .Global = False
        .IgnoreCase = True
        .MultiLine = False
        'find "Word 12345" and replace with empty string
        sTmp = .Replace(sTmp, "")
    End With
    'insert new value
    wsh.Range("A" & i) = sTmp
    Set oRegEx = Nothing
    i = i + 1
Loop

Exit_RemoveMatches:
    On Error Resume Next
    Set wsh = Nothing
    Set oRegEx = Nothing
    Exit Sub

Err_RemoveMatches:
    MsgBox Err.Description, vbExclamation, Err.Number
    Resume Exit_RemoveMatches

End Sub
   
Comments
helloworld2020 6-Nov-12 5:36am
   
Yes this works fine.But, it is only for one column!
Maciej Los 6-Nov-12 15:22pm
   
So what? Change code to your needs.
Tip:
Use Offset(,) function ;)
helloworld2020 7-Nov-12 2:13am
   
Oh, yes you're right. I'm not programming for a long time, so i forgot some basic things. thx again :)
Maciej Los 7-Nov-12 2:27am
   
You're welcome. Only practice makes perfect ;)

Was my answer helpful? If yes, please, rate it (vote).
helloworld2020 7-Nov-12 3:32am
   
I'm having problem with email access.So, I can't vote without account confirmation :S!
Anyway instead using offset, I used this one and works perfect!

For Each c In Worksheets("Sheet1").Range("B1:H10").Cells

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900