how to automate this...

**Arron77** · 02-22-2009, 05:41 PM

Hi all~

I am trying to automate this function, and I don't know where to go about designating the properties, either functions or formats. What I am trying to do is to automate a column so it inserts the following format V.#/no.#, when I type in the numbers with a comma, or slash, or semi-colon to separate them out.

In other words, in case that wasn't clear, I want to be able to enter the numbers and have excel display it as V.#/ no.#. for example I type 2/4 (where the slash indicates the separation of the numbers,) and have excel change it to V.2/no.4, (volume 2/ number 4.)

Can it be done? any help would be appreciative.

Arron

**Paul** · 02-22-2009, 11:32 PM

Hi Arron,

You could try the code shown below. Right-click on the worksheet tab for which you want the code to run, and select View Code. Paste the code below then close the VB Editor window.

The code is looking for any change in column A, and you should enter your numbers separated by a decimal, e.g. 43.56

Please Login or Register  to view this content.

It should work as requested, and if invalid data is entered it will tell you so, as well as undo that invalid entry.

Hope that helps!

**DonkeyOte** · 02-23-2009, 04:20 AM

You could adatp pjoaquin's code slightly to handle multiple delimiters, I think you said you might want to use any of: ; / .

Please Login or Register  to view this content.

**gdfiler** · 02-23-2009, 07:37 AM

Hi Arron,

I've probably read your question differently from the other two posters as my solution is much simpler, although probably a lot more limited. I've tested it on 2007 but am unsure of compatibility on the 2003 version.

Format Cells > Number and under category select Custom

In 'type' copy and paste: "V."0"/no."#

Click OK

You can then type to the cell and it would automatically be formatted. You cannot add a comma, slash or colon, and you are limited to up to only 10 parts (only the last digit is included for the 'no', part)

**DonkeyOte** · 02-23-2009, 07:46 AM

gdfiler,

I totally agree with you that if you assume only one delimiter (period) then using a Custom Format is definitely the way to go (I believe multiple are required in this instance hence the latter VBA routine).

Re: Custom Format I think you have some typo(s), no ?

If you enter 4.2 the following custom format:

"V."0"/no."#

Would generate: V.0/no.4

I think perhaps it should be:

"V."0"/no".#

which would generate V.4/no.2

Custom Format would work the same pre2007.

**gdfiler** · 02-23-2009, 08:06 AM

Hi DonkeyOte,

Thanks for taking the time to look at my suggestion. I'm afraid I don't fully understand what you mean by delimiter but you are right in saying that when you type 4.2 into the a cell with my custom formatting applied, the incorrect cell value will be shown.

This is not because of a typo however but rather I didn't factor a separator in when working this out. Your suggestion of "V."0"/no".# works well if the "." is used as a separator, and "V."0"/no".### removes the issue of being limited to only 9 parts.

G.

**DonkeyOte** · 02-23-2009, 08:24 AM

Originally Posted by gdfiler

...I'm afraid I don't fully understand what you mean by delimiter

To clarify by delimiter I was referring to the character the OP chooses to insert to act as a separator between values... that is to say the character entered to split say 4 and 2... the first post on the thread implies he/she may vary that separator but still expect the same result.. ie entering any one of:

4.2
4;2
4/2

should generate V.4/no.2

If we take the stance that the OP will only ever adopt the first method illustrated (ie period separator -> 4.2) a Custom Format certainly becomes a viable and attractive option!

The key difference between the VBA & Custom Format method is of course the value that persists in the cell after the respective method is applied...

With VBA the underlying cell value becomes V.4/no.2 literally whereas with a Custom Format the underlying value persists as entered (ie 4.2), it is merely the display that is altered.

Establishing which of the two approaches to be preferable depends entirely upon how the source data is to be used subsequently - it may be that the literal text string is required or it may be the case that retaining the value as entered is preferable - unfortunately this is not specified.

Hope that makes more sense...