Something about excel that has not been asked, I think

**public** · 03-24-2014, 12:47 PM

How can I format an Excel worksheet so that when I type in a cell, eg 10K, excel software to interpret it as 10000 (but I want show me 10K)? Is there any way to do this without calling VBA? Please.

**AlKey** · 03-24-2014, 12:51 PM

Custom format and type in #,K

**Bernie Deitrick** · 03-24-2014, 01:02 PM

You cannot do this without using either a helper column of formulas or VBA - Excel will never interpret 10K as 10000 unless you replace the 10K with the number 10000. You can do this automatically by using event code - copy the code, right-click the sheet tab, select "View Code" and paste the code into the window that appears. Then save the workbook as a macro-enabled .xlsm.

Please Login or Register  to view this content.

When you enter 10K into a cell, it will appear as 10K but actually be 10000, so calculations will work using that cell's value.

**public** · 03-24-2014, 01:04 PM

Yeah, thanks, @AlKey, it seems to work.
But I have further questions, namely: how to display instead of K ----> KΩ
I can use the "Custom Format menu" various symbols?

@Bernie, thanks for the reply, I immediately analyze and your solution.

**FDibbins** · 03-24-2014, 01:09 PM

You can add pretty much any text in custom, the way AlKey showed you, so just use KΩ instead of just K

**public** · 03-24-2014, 01:18 PM

OK, I understand @FDibbins.

Now, I tried @Bernie VBA's example and I have a question: if in an excel worksheet there are several formatting - eg K, uF, nF, pF - how should I change the VBA code exemplified by you, so that when I'm typing 100nF, excel interpret this thing as if as 100 x 10^-9, then, when I type 10KΩ --- > 10000 ..... ?
I think VBA is much more appropriate solution because avoiding the start formatting each cell in a certain way.

**Bernie Deitrick** · 03-24-2014, 01:19 PM

You need to use the custom Format #,K and then hold down the alt key and type 234 on the number keypad (to get the Omega to show as Ω.

If you want to use code, it is not as easy to get the format string to work - apply that formatting to a cell, name the cell FCell, and use this code

Please Login or Register  to view this content.

**public** · 03-24-2014, 01:37 PM

@Bernie, your example works fine but it is not 100% correct. If I'm typing in a cell excel 20K and excel cell displays 20000. I want to show my whole 20K in the cells but in the fx (function) line displaying 20000. How I do this? And how to broaden the above code and with other abbreviations, like nF, uF...?

**Bernie Deitrick** · 03-24-2014, 01:37 PM

I don't think there is any way to show 0.0000000X as just X without using a helper cell for actual number storage.

**Bernie Deitrick** · 03-24-2014, 01:47 PM

My code enters 20000 into the cell, which will show as 20000 in the formula bar, but as 20K on the sheet, so I'm not sure what you mean. But the other abbreviations are not possible, AFAIK, since they are below 1.

**public** · 03-24-2014, 02:07 PM

So, I can implement in VBA only one abbreviation (or custom format)? I understood well?

**Bernie Deitrick** · 03-24-2014, 02:23 PM

You can implement as many as you want, as long as they are for numbers > 1000 - millions, billions, trillions. But the decimal presents problems.

**public** · 03-24-2014, 02:30 PM

I would like to implement for:
1) GHz = 1000000; kHz = 1000;
2) uF = 1/1000000; nF = 1/1000000000; pF = 1/1000000000000.
3) MΩ = 1000000; kΩ = 1000.
Can you show an example of VBA for all the examples above? Please.

**Bernie Deitrick** · 03-24-2014, 02:35 PM

No, I cannot - Excel does not have a shorthand way to show 1/1000000 as 1uF; 1/1000000000 as 1nF; or 1/1000000000000 as 1 pF.

**MrShorty** · 03-24-2014, 03:28 PM

I don't know that Excel has a good, preprogrammed way to deal with units plus metric prefixes.

What exactly are you needing here? Excel can be programmed to handle units like this, but you may need to think through a little more exactly what you want it to do.

From the examples given, it looks like you simply need a utility that will "move the decimal" for you depending on the metric prefix for a given entry. Do you need it to do more than that? My first thought for something that just needs to change prefix is to drop the base unit from the input, then use the prefix with "m" (meter) in the CONVERT() function. http://office.microsoft.com/en-us/ma...551.aspx?CTT=1

I almost always find this sort of thing easier to program if I keep the value and unit in separate cells, so each cell contains only one piece of information.

Illustrated in attached spreadsheet.

There are certainly other strategies I use when performing different unit conversions. A lot depends on exactly what I need to do with the numbers after I enter them.