Macro to Automatically Find/Replace

**Cansa** · 09-27-2012, 08:57 AM

Hi there, I'm hoping that this is an easy fix...Currently I am working with Excel 2010 and am trying to find a way that when the letters "Ca" are typed the word will Automatically change to "Caliper", I have to do this with other measuring devices such as "V" to "Vision", "M" to "Mircometer", "T" to "Thread Gage", "R" to "Ring Gage", and "G" to "Gage Pin". Is there a Macro that can accomplish this?

Regards,
Cansa

**Mowgli** · 09-27-2012, 10:05 AM

Hi Cansa,

Would it be possible to post an example of your worksheet, preferably with a before format and after format somewhere? This helps forum members see things such as which column or row the data to be changed will be.

My initial thoughts are that it could be done by a few methods. If it is a new sheet as yet unpopulated then Data Validation may be the way to go, if it is an existing sheet which is already populated with data then it is a different approach.

Mowgli

**Cansa** · 09-27-2012, 10:24 AM

Okay sounds good, the column H is the column where they would enter in the letter, and the autofill would need to finish the word. They enter the information in row by row. I've tried to do this using the autocorrect and adding the letters to the list, but unforutnately that effects the entire document instead of just column H.

Thanks for looking into this!
Cansa

Inspection Report - Formatting Project Master Copy.xlsx

**tigeravatar** · 09-27-2012, 10:36 AM

For something like this, it would be easiest to Add a text entry to the AutoCorrect list

Add a text entry to the AutoCorrect list

Click the File tab.
Click Options.
Click Proofing.
Click AutoCorrect Options.
On the AutoCorrect tab, make sure the Replace text as you type check box is selected.
In the Replace box, type a word or phrase that you [want replaced] — for example, type Ca.
In the With box, type the [replacement] of the word — for example, type Caliper.
Click Add.
Repeat for all items you want auto-corrected.
Click OK.

**swapnilj1** · 09-27-2012, 10:39 AM

Hi Cansa,
Please find the attachement which is replacing the letters to word.

Book1 to replace.xls

**Mowgli** · 09-27-2012, 10:43 AM

Cansa,

I gave 2 options. If you type the first letter, or Ca in the case of Caliper, and press return or tab it will automatically replace the contents of that cell with the correct name. Also, in row 19 I have included a drop down bow using data validation linked to the list I have typed in Coumn R. to remove the drop down box you can just copy a blank cell over the top of it, or to replicate it you can copy it into blank cells.

Neither of these are exactly what you asked for I know but if they do not suit then am sure a more learned member of the forum would be able to help out more

Inspection Report - Formatting Project Master Copy RevA.xlsm