Inserting decimals

Hello.

I'm trying to either write or find a function that will automatically insert a decimal into different types of numbers. My spreadsheets contain three digit numbers and four digit numbers, but I want to auto place a decimal one space to the right of the first number on both of these.

For example:

256 | 1285
255 | 1290
254 | 1295
259 | 1280

I would like to be able to type those numbers but have it come out as:

2.56 | 1.285
2.55 | 1.290
2.54 | 1.295
2.59 | 1.280

Short of having a fixed decimal at 3 and always having to add a zero to any three digit number and having one less decimal point in those boxes, I'm stumped as to how to accomplish this. I would like to only have to type the three numbers and the four numbers without manual placing decimals or adding zeros.
Any help would be great.

Thanks

Try this
Formula in C1

Formula:

Please Login or Register  to view this content.

Row\Col	A	B	C	D
1	256	1285	2.56	1.285
2	255	1290	2.55	1.29
3	254	1295	2.54	1.295
4	259	1280	2.59	1.28

I just posted this question in the macros thread. Thanks for the advice.

And to quekbc, I don't think that formula is going to work because I still need the zeros at the end when I do type them.

I just posted this question in the macros thread. Thanks for the advice.

And to quekbc, I don't think that formula is going to work because I still need the zeros at the end when I do type them.

press ALT-F11 to get to the macros
in the left pane, dbl-click THISWORKBOOK
in the right pane ,paste this code:

Please Login or Register  to view this content.

REMEMBER, macros CANNOT be saved in .xlsx files. Only .xls, or .xlsm

Originally Posted by Tony Valko

I would recommend using an event macro to do this.

I still don't see why you would need a macro for this. Nevertheless, it is more likely due to my personal preference to avoid macros as much as possible.

Originally Posted by trevorwlms

And to quekbc, I don't think that formula is going to work because I still need the zeros at the end when I do type them.

Formula to do what is required.

Formula:

Please Login or Register  to view this content.

Totally understand your view, but to reiterate. (And I say this as someone who knows a fair amount of programming) It is a personal view of mine that anything that can be done without VBA, should be done without VBA as there are quite a few "cons" to using VBA - especially for things as simple as this (e.g. lost of raw data, undo-tree removed, unintended consequences etc.). I find that, because EF is such a solution driven forum, that a lot of these "cons" aren't properly communicated to the person seeking the solution.

ranman - I'm not sure how to make that macro keep the zeros at the end of my numbers.

nigel - How do I write that formula to include multiple cells.

This is a sample file I'm working with if that helps.
1829JP - ABEWR 1502.xlsx

Strongly agree. But this doesn't happen often here. Solutions are "tailored" to solve a specific problem without any or minimal mention of potential consequences. Which is also understandable as (1) the solutions are provided voluntarily and no one is going to spend too much time on it to "dummy-proof" and (2) more often than not we do not have enough information to know what the potential consequences are.

I shall stop now as I'm pretty sure some moderator is going to start making a fuss about this digression.

Hi trevorwlms,

See the attached file for a VBA solution to your question.

The following code is in the ThisWorkbook module of your file:

Please Login or Register  to view this content.

Lewis