Macro removing word from string

alvbnp

Board Regular
Joined
Jun 26, 2006
Messages
180
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.
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Krishnakumar

Well-known Member
Joined
Feb 28, 2003
Messages
2,615
Hi,

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

alvbnp

Board Regular
Joined
Jun 26, 2006
Messages
180
Thanks guys. But after the replacement, it left a blank space in front, how can I get rid the blank space?
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995

ADVERTISEMENT

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

Board Regular
Joined
Jun 26, 2006
Messages
180
Excellent. Silly me being not able to find it out.

Thanks again.
 

Stormseed

Banned
Joined
Sep 18, 2006
Messages
3,274
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:
 

Forum statistics

Threads
1,136,370
Messages
5,675,367
Members
419,566
Latest member
moni2277

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top