in an effort to be able to enter data quickly I need Numbers to convert one number into a decimal format. ie. I enter 16168 and Numbers converts it to 1.6168 (the current price of the Britsh Pound in US dollars GPB/USD)
I would also like to enter text and have it converted to all caps. e.g I enter gpb/usd and it automatically changes it to GPB?USD
Thanks!
iMac, Mac OS X (10.6.7)
"I enter 16168 and Numbers converts it to 1.6168"
Numbers can display the value you enter in a number of formats, but what you are asking her is for Numbers to change the value itself, not just the format. That requires a formula, and the formula must be in a separate cell from the one in which you are entering the value, and will depend on defining a fixed relationship between what you enter and the expected result.
Will the entered value always have five digits, and the result value always be 1/10,000 of the value entered.
=B/10000
Will the result always be in the form N.nnn..., with a single digit before the decimal, and a flexible number of digits following the decimal? Format the entry column (B) as Text, and use this formula in the result column:
=LEFT(B,1)&"."&RIGHT(B,LEN(B)-1)
"I enter gpb/usd and it automatically changes it to GPB?USD"
"?" ?. Probably not. Did you mean "changes it to GPB/USD" (or, more likely 'changes gbp/usd to GBP/USD')?
If this is the only type of text you're entering, the simplest method would be to set the caps lock key. Caps lock affects only the letter keys, and unlike the Windows implementation, the Mac's Cap Lock key overrides the Shift key—with caps lock down, all letters are upper case regardless of the position of the shift key.
If you're entering mixed text, and want to convert only the gbp/usd, and similar conversion notations, your best route might be the Symbol and Text Substitution feature in the Auto Correction section of Numbers Preferences:
Click the + sign at the bottom of the preferences pane, type the 'entry' text into the left column and the replacement text into the right column. Note that I did not enter the same character sequence into both columns. I tried that, then entered the lower case version into cell B2 of the table visible behind the preferences pane. There was a slight blink on pressing return to move on to the next cells, an indication that the substitution actually did take place, but as you can see from the result in that cell the substituted text adopted the lower case format of the exact same characters it replaced.
After deleting that pair and entering the pair shown into the preferences pane, I entered 'gbus' into cell B3 of the table and pressed return. Result, as shown, is what you're looking for.
If your conversions are always to USD, you can likely create a set of two-character keystrokes that will bring up each one. If you also need the reverse conversion, simply adding a u in front of the two character string would be enough to identify the substitute string needed. eg. ch > CHF/USD, uch > USD/CHF.
OR
You could use a formula in a separate cell/column:
=UPPER(B)
Regards,
Barry
OK. I found that if you highlight a column and got to Format>Font>Capitalization you can choose to capitalize everything in that row/column. I like your idea about the short keys/abbrviations.
There's got to be another way to automatically change the deciimal on a number. I know it can be done in excel....
Actually what your most likely thinking of in excel is just a display trick using custom formatting. The underlying number is still the whole number you typed in. It DOES NOT Convert anything, just displays a decimal in the proper place.
i.e. It will show you 12.456 but if you multiply that number by 1 in another cell it would show you 12456. (I just did this and validated my answer).
The only other way of doing this in excel is through a macro (VBA), which would execute on a Cell Change. You could do something similar using applescript, however you would have to execute yourself.
Jason
I need numbers to covert a number into a different format after entry
