Phonetic Help

Hi Ryusui,

I put together this code which might do the trick. It relies on a table of values/phonetics on Sheet1, cells A1:B36 (A-Z, 0-9). Obviously this location could change, or you could use other methods to check each letter against some list.

Select the cell you want to convert to phonetics, then click the command button and it will populate the cell below with the phonetics separated by two dashes each. The code also eliminates any space characters using Substitute, but this could also be modified.

Please Login or Register  to view this content.

Wow, pjoaquin! Thank you so very much! This is exactly what I needed. And an especially large thanks for working this so quickly!

-----------------------------------------------------------------------
Actually, now that I'm looking at it, is there a way to modify that so when certain cells have text put into them, the phonetic automatically appears beneath it? I'm going to have several cells with data that need converting and to select each cell and hit the button for every one will get a little tedious.

Hi again,

Right-click on the worksheet tab on which you'll be entering the values, select 'View Code'. In that code window paste the following code:

Please Login or Register  to view this content.

Any word(s) you enter in Column A (any cell) will trigger the macro. The phonetic version will appear in the cell below the one you just changed. I also added some error trapping in case non-alphanumeric characters are encountered (e.g. symbols, punctuation, etc.). Hopefully this works for you.

just out of interest when did the phonetic alphabet change to p=pencil?
having worked in telecomms for over 30 years and use it on a regular basis
the accepted international format is

A - Alpha // K - Kilo // U - Uniform // 0 - Zero
B - Bravo // L - Lima // V - Victor // 1 - Wun (One)
C - Charlie // M - Mike // W - Whiskey // 2 - Two
D - Delta // N - November // X - X-ray // 3 - Tree (Three)
E - Echo // O - Oscar// Y - Yankee // 4 - Fower (Four)
F - Foxtrot // P - Papa // Z - Zulu // 5 - Fife (Five)
G - Golf // Q - Quebec // 6 - Six
H - Hotel // R - Romeo // . - decimal (point) // 7 - Seven
I - India // S - Sierra // . - (full) stop // 8 - Ait (Eight)
J - Juliet // T - Tango // 9 - Niner (Nine)
p for pencil is like a schoolkids thing
a=apple b=bear
c=christmas and so on

Martin, I made all those up. I just needed something in those fields to test the code.

Ryusui, to fix that problem, add the one line of code in red shown below. My guess is that you get an error when trying to delete multiple cells at the same time, but not when you delete just one cell at a time (at least this was the case for me).

Please Login or Register  to view this content.

Ryusui,

In my last code offering, I simply replaced A:A with B4,I4,B9,I9,B17,I17,B22,B33,I33,B38,I38,B43,I43 and I can delete any amount of cells without error.

Remember to leave the quotation marks around those cells, e.g. Range("B4,I4,B9,I9....").