Can a formula be written in excel or VBA to perform the "text to column" function ?

Hello I am new to the group and need help with a worksheet I am working on.

I have a column on an excel worksheet (excel 2003)  which contains the name and description of a retail product, the retail price, the labor price to install it (which are selected from a drop down list) .  I would like to be able to expand that column into three columns with a macro.  These separate fields are separated by a comma at present and if I use the  text to column feature it does work but due to un-computer savy employees I would like a button for them to push to perform this function after they have made their entries.

Can this be done and how?

Thank you!

Lynn

Why not record a macro to do the Text to Columns bit, and then assign that macro to a bjutton?

Bob

Thank you for your quick response.  That is what I was thinking of doing however I am new to macros and have take a tutorial on them but am shy of knowledge as to how to really do this.

If you know where more tutorials are or can help me with this I would be so grateful!

Thank you

Lynn

If you use the macro recorder, it will create the VBA for you.

I hate to sound dumb but  macro recorder?

The tutorials I took on the excel website used the maco function but I would have to write the formula in vba myself and I don't know vba language enough to do it.  At least I am at a loss at present.

The site said I could use the text to column funtion on the previously merged information in my cell which does work but I would like my employees to just click a button and have it hapen for them...less mistakes that way ;)

Is there a place to go to find more tutorials?

Thanks

Lynn

See http://www.excel-vba.com/vba-prog-1-7-excel-macro-recorder.htm

Bob

Thank you Thank you!!

So far so good! 

I did get it to write the code and function for the one line.

To have it do the same for multiple lines do I highlight multiple cells at the beginning or is there another way to do it to get the macro to repeat it in that cell for each line on the worksheet?

Lynn

Bob

Ok I did figure it out for all the lines so far.

I am having a problem getting the control button to work.

I create the button and then create the macro but the button does not work.

Any suggestions?

Lynn

This forum allows attachments, so can you post your workbook and I will look at it for you.

Bob

I would love to do that but I don't see an attachment button

do I just copy paste?

Lynn

I finally found the attachment button so here is the file...I hope

l

ynn

Lynn,

I wouldn't use Text To Columns with yor data setup as it, as the dataset is so small I would use a simple lopp macro and test the current setup.

BTW, the way that you are creating dependent dropdowns will run out of steam very quickly, pre-2007 Excel only allows 7 nested functions.

I have another way of creating dependent dropdowns which allows any number., It is a tad complex, but I could insert it if you wish.

Regards

Bob

On second thoughts, I would add a worksheet change event to C15-C42 to split it on entry.

Bob

I would be interested in the more advanced way of attaining more dependent drop down menus, but would need to be able to re-create or add to the menu items esp next year when pricing or stocked items changes, so i would need to be able to understand the formula.

Also the loop macro sounds good to me, but I may be in over my head so any help you are willing to share is greatly appreciated!

Lynn

Lynn,

Try this.