Macro removing word from string

[alvbnp]

My column A having a long text data. I need a macro that will read through the whole column A and remove the word 'finance'.

eg:
finance abc efg
finance ghdg jkljs
finance yhuihj-kjlj kk.l
finance www zzz sss.ss

It's always the same word, always in the front.

Need a macro for it, I will use it as part of my existing macro.

Thanks.

 

[Krishnakumar]

Hi,

Columns(1).Replace What:="finance", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False

 

[Barrie Davidson]

Columns("A:A").Replace what:="finance", replacement:=""

 

[alvbnp]

Thanks guys. But after the replacement, it left a blank space in front, how can I get rid the blank space?

 

[jindon]

Thanks guys. But after the replacement, it left a blank space in front, how can I get rid the blank space?

Hi
change "finance" To "finance "

Or
after the replace

=Trim(a1)

 

[alvbnp]

Excellent. Silly me being not able to find it out.

Thanks again.

 

[Stormseed]

Excellent. Silly me being not able to find it out.

Thanks again.

hey mate...I have come across these things a number of times...instead I used the Ctrl + H (Find & Replace) combination which infact is so **** easy ! Type "Finance" in the find option - leave replace as blank (do not put a space) and then press Alt + A. It will do the needful.

Moreover I used to do conditional formatting for highlighting the words which I need to eradicate from the given range. Just for a detailed view in a large data range ! U can use this formula in the Conditional formatting option...

=NOT(ISERR(SEARCH("Finance",A1)))

It will highlight all the "Finance" words according to the formatting u select in the Conditional Formatting dialog !

take care :wink: