Code to delete text left of a specific character

**Yoshi64** · 10-11-2014, 10:34 PM

I am writing a VBA macro which delimit a column of raw text into usable data. I figured out how to delimit specific characters, but I still need to clean it up. Some of the cells are preceded by a date and name, such as...

Date: Name: 100
150
125
Date: Name: 225
275

...and I want to remove all that text from the cell. So basically a code that will delete any text to the left of any ":"

Thanks!

**xladept** · 10-11-2014, 11:04 PM

With nothing else to go on this will fix your active cell:

Please Login or Register  to view this content.

**sktneer** · 10-11-2014, 11:20 PM

Assuming the strings are in col. A, you may also try this.......

Please Login or Register  to view this content.

**Vikas_Gautam** · 10-12-2014, 03:24 AM

I guess its simple..
Try find and replacing "*:" with Nothing ("") without Quotes
To be more precise, you can select the Target Range of cells where in the replacement has to be done...
or you can try this simple

Please Login or Register  to view this content.

Check the attached file..

**xladept** · 10-12-2014, 02:58 PM

A more useful form:

Please Login or Register  to view this content.

**Vikas_Gautam** · 10-14-2014, 11:07 PM

Thanks for your valuable response Yoshi...

**Marc L** · 10-15-2014, 02:54 AM

Please Login or Register  to view this content.

**Yoshi64** · 10-19-2014, 03:06 PM

Hey guys

Very sorry I lost track of this thread/project for the past week and didn't respond! Skneer's code was originally working for me, but now when I try to use it, it seems to not be working properly. Not sure if I changed some setting in excel or what.

I am now using xladept's code with one revision, which now causes the code to be performed on only row A rather than the entire sheet:

Please Login or Register  to view this content.

Again my apologies for my lack of follow up. Thanks to everyone for the help!

**xladept** · 10-19-2014, 04:33 PM

You're welcome and thanks for the rep!

**Marc L** · 10-19-2014, 07:26 PM

Should be faster by combinating Find method and Split function …

**xladept** · 10-19-2014, 08:51 PM

@ Marc L - Maybe faster is relative - the one operation will be bypassed by my code but your code would process every one??

**Krishnakumar** · 10-19-2014, 10:46 PM

What about this ?

Please Login or Register  to view this content.

**Marc L** · 10-20-2014, 02:02 AM

Originally Posted by xladept

@ Marc L - Maybe faster is relative - the one operation will be bypassed by my code but your code would process every one??

???

Split only cells from Find method …

**xladept** · 10-20-2014, 01:41 PM

@ Marc L - like this?

Please Login or Register  to view this content.

**Marc L** · 10-20-2014, 06:48 PM

In the great lines …

Please Login or Register  to view this content.

**Marc L** · 10-21-2014, 04:44 AM

Thanks for the Rep' !

TBTO rule : Think, But Think Object !

**Marc L** · 10-21-2014, 05:12 AM

See also post #12 great code ! EVALUATE is a powerful function able to work as a matrix formula.

**apo** · 10-21-2014, 05:38 AM

Better late than never

Try:

Please Login or Register  to view this content.

Note: I have assumed you have nothing in Column B.. if you do.. just change the first line to suit..

Code to delete text left of a specific character

LinkBack

Thread Tools

Rate This Thread

Display

Code to delete text left of a specific character

Re: Code to delete text left of a specific character

Re: Code to delete text left of a specific character

Re: Code to delete text left of a specific character

Re: Code to delete text left of a specific character

Re: Code to delete text left of a specific character

Another way …

Re: Code to delete text left of a specific character

Re: Code to delete text left of a specific character

Re: Code to delete text left of a specific character

Re: Code to delete text left of a specific character

Re: Code to delete text left of a specific character

Re: Code to delete text left of a specific character

Re: Code to delete text left of a specific character

Re: Code to delete text left of a specific character

Re: Code to delete text left of a specific character

Re: Code to delete text left of a specific character

Re: Code to delete text left of a specific character

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] How to use LEFT and RIGHT to delete last character

[SOLVED] Extract text to left of nth character

[SOLVED] Find text left or right of a specific character in a text string

[SOLVED] Left/Right function in VBA, finding a specific starting character

Macro to find character and delete all text in cell after the character

Bookmarks

Bookmarks

Posting Permissions