Phonetic Alphabet - Help Please

Phonetic Alphabet – Help please

I am, currently working on a project for school. To make my life LOTS easier I would like to make a spreadsheet that I can type a sentence in one box and set the answer in the second box.

Example:

Type-
A1: Thank you for your help.

Result:
A2: tango hotel alpha november kilo yankee oscar uniform foxtrot oscar romeo yankee oscar uniform romeo hotel echo lima papa decibel

Is there a way to do this?
I don’t know much about excel so your patience.
-Robert-

Hi!

I don't know what "production line" you want to operate, but try this for starers.

Build a table with a,b,c,etc in one column and alpha, bravo etc in the next.
Name this Table1 (select the table: Insert > Name > Define and name it)

Now put a sentence in A1 and the following formula in B1:

=VLOOKUP(MID($A$1,COLUMN()-1,1),Table1,2,FALSE)

Copy this formula across as many columns as you have letters and spaces in your longest sentence.

Each column will now give you a phonetic value for a character in the sentence..

Spaces in the sentence will appear as #N/A. If that is a problem, it can be cured.

Will this help?

Alf

I must apologize I don't know what a "production line" is.

When I click on Data>Table it asks for a Row input cell and a Colum input cell.
I currently have Sheet 2 A1-26 lettered from A-Z and B1-26 the phonetic alphabet.
I guess I just don't know how to set up a table...

After looking at the formula that you posted I guess that i am to create a table on Sheet 1 and name it Table1 than put the formula in B1 and type in A1? i think...

How do I create a table for my A-Z and Alpha-Zula on sheet 1?

Hi!

Forget about the "production line". I didn't know whether you wanted to do masses of scripts or the odd one or whatever.

You already have your a-z alpha-zulu table on sheet2. Let's use that.

Highlight this table from A1:B26.
Choose Insert on the main menu: then choose Name from the dropdown menu and then choose Define from the next fly-out menu.
This will give you a dialogue box. Type Table1 in the top line. The bottom line should read =Sheet2!$A$1:$B$26.
Now click OK and the table is set up.
Go ahead with the formula.

I had assumed (on the flimsiest of evidence) that we were dealing with lower-case letters. You used upper-case in your latest post.
For simplicity's sake, use whichever you like but use it consistently in the table and in you sentences. Otherwise we have to take other action. Not difficult, but probably best avoided at the moment!

Let me know how it goes.

Alf

Robert,

Here is a macro that will do what you need. Copy the code below to a module in your Excel file. After you have done that, select a cell that you want to translate and then run the macro. It will put the translated phonetic alphabet in the cell to the right of the selected cell:

Please Login or Register  to view this content.

Hope this helps,
theDude

AlfD,
Your code works for the first letter of the word. Is there a way to make it show all the letters of the word?
Thank you for the talbe help

-Robert-


theDude,
Ummm... I have no idea what your talking about... I don’t know how to create a macro. Can you please tell me the steps to get one in there? Your code looks great but I just don’t know how to use it

-Robert-

Robert,

To copy the code I provided to your Excel workbook, do the following:

1) Open the Excel file you want to copy the code to.

2) After the file is opened, use menu 'Tools', then select 'Macro', then select 'Visual Basic Editor'.

2) On the left-hand side of the screen, there should be a frame titled 'Project - VBA Project'. If it isn't visible, use menu 'View', then select 'Project Explorer'.

3) In the Project Explorer frame, there should be an icon showing your open Excel file and it will be named something like 'VBA Project (your file).

4) Click on the file name to select it, then use menu 'Insert', then select 'Module'. On the right side of your screen, a blank white page appears (this is the module you just inserted).

5) Switch to the Excel forum and select the code I provided, then copy it.

6) Switch back to the blank module you just created, click it and then paste the code into the module.

7) Save the file.

8) Close the Visual Basic Editor.


To use the macro you just copied, do the following:

1) In the file you just copied the macro to, either type some text into a blank cell, then select the cell or select a cell that already has text.

2) Use menu 'Tools', then select 'Macro', then select 'Macros...'

3) In the Macros dialog box, select the 'Macros in:' dropdown and select the option 'This Workbook'.

4) Select the macro 'phonetic', then click the 'Run' button. In the cell to the right of the selected cell, the phonetic alphabet translation will appear.

Have fun!
theDude

Hi Robert!

Did you remember to do this bit from my original post?

Copy this formula across as many columns as you have letters and spaces in your longest sentence

Do you know how to "copy across"?

Alf

Thank you both!

They both work perfectly!!!

AlfD,
This is great!!! I can use this for so many other things that I am doing! *Big cheesy smile*

TheDude,
This is great as well! I am working on another project that I may be able ti use something like this. A few questions for ya.
1) Is there a way to run without having to go into the macro menu to make it run?
2) Is there a way to make each word go into its own box?

-Rob-

Hi!

Happy to help. Good luck with the projects.

Alf

Rob,

To answer your questions:

1) Is there a way to run without having to go into the macro menu to make it run?

Yes, you can assign "shortcut keys" to the macro...This is from the Excel Help file:

On the Tools menu, point to Macro, and then click Macros.

In the Macro name box, enter the name of the macro you want to assign to a keyboard shortcut.

Click Options.

To run the macro by pressing a keyboard shortcut key, enter a letter in the Shortcut key box. You can use CTRL+ letter or CTRL+SHIFT+ letter, where letter is any letter key on the keyboard. The shortcut key will override any default Microsoft Excel shortcut keys while the workbook that contains the macro is open.
To include a description of the macro, type the description in the Description box.

Click OK.

Click Cancel.

2) Is there a way to make each word go into its own box?

Yes... I'm assuming you want each word in a separate cell moving to the right of the cell you want to translate.

NOTE: This will limit the text you want to translate to 255 'translateable' characters (A-Z, 0-9 and decimal/period punctuation) because Excel spreadsheets have a limit of 256 columns (the first column is taken up by your text). In my original code, the limit varies based on what characters are in the cell, but since "N" translates to an 8-character word "November" (the largest of all phonetics), the maximum limit of your text if all were "N" characters would be 4,095 characters (Excel limit of 32767 characters in a cell divided by 8).

So replace the end of your code:

Please Login or Register  to view this content.

With this code (I 'commented out' the original by placing a ' in front of it so you can revert it back if you need to):

Please Login or Register  to view this content.

Hope this helps,
theDude