Macro to delete characters between 2 characters.

**abjac** · 09-29-2014, 04:14 AM

HI I need help to make a macro to delete all characters between 2 characters.
Thanks to PCI I have sorted allot out all ready, but need this general last thing.
I Need a macro which will delete all characters after 74 from the left and the second - from the right.

If I can have that I will manage to delete what I don't need.
In below example I need this to be deleted which are in red.
C:\Documents and Settings\Alancecille\Desktop\torrents\Karaoke songs\ 0002 --thm - Ig Joe Turner - Shake Rattle & Roll.zip
C:\Documents and Settings\Alancecille\Desktop\torrents\Karaoke songs\ 0003 ps12520 - Jerry Lee Lewis - Great Balls Of Fire.zip
C:\Documents and Settings\Alancecille\Desktop\torrents\Karaoke songs\ 0004 jkdjk -- - Jimmy Buffett - Margaritaville.zip

P.s The macro has to work in column B start from B2 all the way down

Please have a look. I need a macro because it have to be used with other macros.

Thanks in advance

Sincerely
Abjac

**nathansav** · 09-29-2014, 04:19 AM

strResult = mid(strInput,1,firstchar) & mid(strInput,firstchar+2,len(strinput)-firstchar+2)

**Richard Buttrey** · 09-29-2014, 05:23 AM

Hi,

Assuming column C is blank and can be used as a temporary working column

Sub DeleteCharacters()
    Dim lLastrow As Long, st As String, x As Long
    lLastrow = Range("B" & Rows.Count).End(xlUp).Row


    Range("C1:C" & lLastrow) = "=LEFT(B1,74)&RIGHT(B1,LEN(B1)-FIND(""|"",SUBSTITUTE(B1,""*- "",""|"",1)))"
    Range("C1:C" & lLastrow).Copy
    Range("B1").PasteSpecial (xlPasteValues)
    Range("C1:C" & lLastrow).Clear
End Sub

**abjac** · 09-29-2014, 04:28 AM

HI Nathansav and thanks allot. Could you please show the full code,
And also if it could be made so it take from 74 from the left, in this case I can find out change it if nessasary.

Thanks in advance

Abjac

**nathansav** · 09-29-2014, 04:35 AM

That is the full code.

**abjac** · 09-29-2014, 04:44 AM

HI I put it like this but it don't delete anything.

Sub test()

Sheets(1).Activate

strResult = Mid(strinput, 1, firstchar) & Mid(strinput, firstchar + 2, Len(strinput) - firstchar + 2)
End Sub

I have put the test sheet in here now. Please have a look thanks
All in read should be deleted
Abjac

**nathansav** · 09-29-2014, 04:47 AM

You have to put your title in the strInput string, and first char is what ever number you want to do it from..

**millz** · 09-29-2014, 04:47 AM

Try:

Sub a()
    Dim s
    s = "C:\Documents and Settings\Alancecille\Desktop\torrents\Karaoke songs\ 0003 ps12520 - Jerry Lee Lewis - Great Balls Of Fire.zip"
    MsgBox Left(s, 75) & Mid(s, InStrRev(s, "-", InStrRev(s, "-") - 1))
End Sub

**abjac** · 09-29-2014, 05:03 AM

Hi All. Maybe its me write it wrong the string
C:\Documents and Settings\Alancecille\Desktop\torrents\Karaoke songs\ 0002 --thm - Ig Joe Turner - Shake Rattle & Roll.zip
Is not a string I shall open, its how it stand in the cell. Its extracted to column B from the folder, so the path is put in column as a string text. That text I need to change and after I need to change the file name.

Nathansav. sorry maybe I am lost hehe. But please include if you know the full code.
It has to work from column B2 and down. So it delete after the criteria in the string in every cell

Thanks in advance

Abjac

**abjac** · 09-29-2014, 05:42 AM

HI Richard and thanks. Just tested it. In the test sheet I uploaded it delete what I have in cell B2 to B3 and all there is left is words #VALUE!. I changed to B2 and C2 because it start there. But also tried without that from cell B1. I use excel 2003.

Thanks

Abjac

**Richard Buttrey** · 09-29-2014, 06:15 AM

Hi,

I'm attaching the workbook I used for testing. (Sheet1). Check this out first and if this works and yours doesn't then upload the workbook you're using

**abjac** · 09-29-2014, 07:15 AM

Thanks Richard yours is working but the weird thing is when I pasted your code in to my test sheet it don't work. But I saved yours as a xls file and its working. But if you download upper file I uploaded here. It will not work there. I cant find out why really, its really strange. But its working your for sure so thanks allot.

Is it difficult after to add a space between the 4 digits and the - Like this 0002 - xxxxx, It come out with this now 0002- xxxxx.
But try download my first sheet i uploaded here its really weird its not working there.

I changed the code to fit that it start from row 2 like this.

Sub DeleteCharacters()
    Dim lLastrow As Long, st As String, x As Long
    lLastrow = Range("B" & Rows.Count).End(xlUp).Row


    Range("C2:C" & lLastrow) = "=LEFT(B2,74)&RIGHT(B2,LEN(B2)-FIND(""|"",SUBSTITUTE(B2,"" - "",""|"",1)))"
    Range("C2:C" & lLastrow).Copy
    Range("B2").PasteSpecial (xlPasteValues)
    Range("C2:C" & lLastrow).Clear

End Sub

But have a look please on the sheet I uploaded and if you can fix the space it would be great.

Thanks

Sincerely
Abjac

**Richard Buttrey** · 09-29-2014, 07:30 AM

Hi,

See attached.

Any problems let me know.

**abjac** · 09-29-2014, 08:29 AM

HI Richard it works like a charm great. Thanks allot for being patience and help with this. Great help

Have a nice day.

Sincerely
Abjac

**Richard Buttrey** · 09-29-2014, 08:48 AM

My pleasure to have helped and thanks for the rep.

Macro to delete characters between 2 characters.

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Macro to delete characters between 2 characters.

Re: Macro to delete characters between 2 characters.

Re: Macro to delete characters between 2 characters.

Re: Macro to delete characters between 2 characters.

Re: Macro to delete characters between 2 characters.

Re: Macro to delete characters between 2 characters.

Re: Macro to delete characters between 2 characters.

Re: Macro to delete characters between 2 characters.

Re: Macro to delete characters between 2 characters.

Re: Macro to delete characters between 2 characters.

Re: Macro to delete characters between 2 characters.

Re: Macro to delete characters between 2 characters.

Re: Macro to delete characters between 2 characters.

Re: Macro to delete characters between 2 characters.

Re: Macro to delete characters between 2 characters.

Thread Information

Users Browsing this Thread

Similar Threads

Macro to delete row if the first characters are numbers

[SOLVED] Delete characters and add * in front and last characters in ACCESS QUERY

[SOLVED] Characters.delete only works with less than 255 characters?

Macro to Delete all but first 3 characters

macro to delete all characters in a row before @ character

Bookmarks

Bookmarks

Posting Permissions