How to Automatically convert simple words or phrases to symbols

[anthony777]

Hello,

I currently have to use custom symbols to generate a report. Generating this report currently takes hours because I have to scroll through the symbols and insert the symbol as needed. I am attempting to create a macro that will automate this process, however, I need help.

Is there a function in Excel which will take plain text and convert it to a symbol? I have been experimenting with "VLookup" but it is not enough to accomplish what I need. What I need is to type text into a cell and have that text automatically converted into a corresponding symbol.

Any help on this would be greatly appreciated. View the attached file for an example.

[cytop]

The basic problem is you cannot type text in a cell and have a formula (in the same cell) replace that text with something else... and formulas can only update the cell they are so you cannot have a formula in another cell either... it can be done with VBA, though...

[anthony777]

Yes, I believe it can be done in VBA as well... unfortunately that is beyond my understanding

[FlameRetired]

I hesitate to suggest it, but have you tried Auto correct? File > Options > Proofing > Auto correct options.

I say that I hesitate because these carry over into Word also ... and other places inconvenient. While you can override them it is a pain.

[anthony777]

Auto-correct will only work with default symbols, the symbols I will be using are from a font that was created for this task.
I tried assigning CTRL+ALT shortcut keys but this can only be done in Microsoft word and the changes are not carried over to excel.

[FlameRetired]

So copy / paste the literal font-specific symbols into Auto-correct doesn't work in Excel?

Edit No it doesn't.

[cytop]

Sorry, should have kept an eye on this - the issue with the font is the only reason I didn't mentions Auto Correct.

Another complication is that only part of the cell is to be formatted, so VBA is the only solution.

Please Login or Register  to view this content.

Open the VBA editor (Alt-F11), double click on the worksheet this is to be used on in the Project Explorer (Ctrl-R if not visible) and paste. I'm assuming there's no existing code so replace anything in the code window with the above. Note the file will have to be saved as an XLSM file (Use Save As and change the file type in the drop down below the directory listing). and you will be prompted to enable macros when you load the file unless it is placed in a Trusted Location.

For simplicity, this just replaces 1 occurrence of a term per cell...

[anthony777]

Thanks!

Just for clarification I should remove the line in red and simply replace "Windings" with my custom font name.

Please Login or Register  to view this content.

[cytop]

I don't have the font you're using so testing using Wingdings. Remove line above, uncomment line below

Uncomment means remove the apostrophe (') at the start of the line as that makes the compiler treat the line as a comment and it is ignored. There's no need to add the font name, it takes it from the cell the symbol comes from. If you should decide to change the font then no need to change the code.

Also note...

For simplicity, this just replaces 1 occurrence of a term per cell

[anthony777]

I have made the changes and when I type "Datum A" in cell D2 the text does not change.

*Addition*
I also noticed that if I type anything into any of the cells in column A it will automatically change the text to the corresponding symbol in column B. My goal is to be able to type the text that is in column A anywhere in the workbook and have that automatically change to the corresponding symbol in column B.

This is exactly what I have in VBA sheet2

Please Login or Register  to view this content.

I appreciate your help and patience!

[cytop]

Reading code is not rocket science, writing it even less so as it tells you when you make a mistake (code only of course, not logic, and then just most times) - so give it a whirl. Try reading/understanding what each line does and see if you can pick out the bit that needs to be removed...

[cytop]

But, if you need a hand...

Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[anthony777]

thank you, hopefully someday soon this will seem a little easier than rocket science to me.

now that I have an example to work from I can familiarize myself with the VBA language.

[anthony777]

Now how would I edit this to make it effect the entire workbook?

[anthony777]

Disregard the last comment i believe I have figured it out.