Replace Text in Cells

**fish221171** · 02-19-2014, 11:25 AM

Hi All,

I am sure this is simple but I have the following information :

A1

CISCO SYSTEMS
Cisco
HEWLETT-PACKARD
Hewlett Packard

What I would like the formula to do is search A1 and if it finds CISCO SYSTEMS make this Cisco, the formula at the same time also needs to check for the word HEWLETT-PACKARD and make this Hewlett Packard.

So the end result would be:

B1
Cisco
Cisco
Hewlett Packard
Hewlett Packard

I don't want to use search and replace as I am trying to build a sheet with vlookup's etc and want to keep this in a formula or funcation.

Thanks for your help.

**alansidman** · 02-19-2014, 11:28 AM

Are these the only two criteria you wish to change or are there more?

**fish221171** · 02-19-2014, 11:34 AM

Hi Alansidman,

Thanks for the reply.

For now I only have these but I may wish to add some more depending on the data when it comes to me in RAW format, so making this easy to append to would be great (maybe a VBA script if it's easier) otherwise I guess the lines in the cells could get long!. If you know what I mean....

**AlKey** · 02-19-2014, 11:38 AM

try this

=SUBSTITUTE(SUBSTITUTE(PROPER(A1),"Systems",""),"-"," ")

	A	B
1	CISCO SYSTEMS	Cisco
2	Cisco	Cisco
3	HEWLETT-PACKARD	Hewlett Packard
4	Hewlett Packard	Hewlett Packard

**alansidman** · 02-19-2014, 11:43 AM

Here is one possibility. It is case Sensitive, so if you look for Cisco Systems it will not find it, but if you look for CISCO SYSTEMS, then it will work.

Please Login or Register  to view this content.

How to install your new code

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

Press Alt-F8 to open the macro list
Select a macro in the list
Click the Run button

**fish221171** · 02-20-2014, 05:54 AM

Thanks all, works great.

**martindwilson** · 02-20-2014, 06:09 AM

did you not consider just find replace?
find CISCO SYSTEMS
replace Cisco
find HEWLETT-PACKARD
replace Hewlett Packard

**AlKey** · 02-20-2014, 09:02 AM

You're welcome and thank you for your feedback!

Please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).

Replace Text in Cells

LinkBack

Thread Tools

Rate This Thread

Display

Replace Text in Cells

Re: Replace Text in Cells

Re: Replace Text in Cells

Re: Replace Text in Cells

Re: Replace Text in Cells

Re: Replace Text in Cells

Re: Replace Text in Cells

Re: Replace Text in Cells

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Replace text for selected cells only

[SOLVED] Cells values to be check and replace with specific text

Search Cells and Replace Text in that cell

How do I replace the last entire word in text cells?

[SOLVED] Find/Replace Text In Cells

Bookmarks

Bookmarks

Posting Permissions