code for text to columns

Let's say I want a worksheet to do "text to columns" (delimited) ever
time it opens?

For example: Text to columns, delimited, by space for column A.

Thanks again all...Vat

--
Message posted from http://www.ExcelForum.com

0
7/19/2004 6:46:26 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
294 Views

Similar Articles

[PageSpeed] 19

Hi, Vato,

Add this code to your main sheet module1.bas.(In VB
editor, menu Insert->Module)

Sub auto_open()
    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), 
DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, 
ConsecutiveDelimiter:=True, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=True, 
Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1)), 
TrailingMinusNumbers:=True
End Sub

jeff

>-----Original Message-----
>Let's say I want a worksheet to do "text to columns" 
(delimited) every
>time it opens?
>
>For example: Text to columns, delimited, by space for 
column A.
>
>Thanks again all...Vato
>
>
>---
>Message posted from http://www.ExcelForum.com/
>
>.
>
0
anonymous (74722)
7/19/2004 7:45:58 PM
Vato

Try recording yourself doing it.

Tools>Macros>Record new macro...

If the generated code needs modification, or doesn't do exactly as you need, 
post back with an updated description of your need beyond what you have

-- 
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS


"Vato Loco >" <<Vato.Loco.19nmjc@excelforum-nospam.com> wrote in message 
news:Vato.Loco.19nmjc@excelforum-nospam.com...
> Let's say I want a worksheet to do "text to columns" (delimited) every
> time it opens?
>
> For example: Text to columns, delimited, by space for column A.
>
> Thanks again all...Vato
>
>
> ---
> Message posted from http://www.ExcelForum.com/
> 


0
7/19/2004 7:50:41 PM
Reply:

Similar Artilces:

How can I delete a list from a column?
Hello all -Excel 2003- I have a column L that call a list. When I want to insert a new column in M, this M columns takes also the list of L. I have now the same list in L and M. How can I delete the list from M? Thanks a lot. Select the column and Edit>Clear>Contents. Gord Dibben MS Excel MVP On Mon, 14 Aug 2006 11:20:51 +0200, "Casrlos A." <carlin334@msn.com> wrote: >Hello all -Excel 2003- > >I have a column L that call a list. When I want to insert a new column in >M, this M columns takes also the list of L. >I have now the same list in L a...

Reply marking in original email text
I can't find how to turn on the reply marking (ie my name) when replying to an email by inserting the reply within the body of the original email. I am using Outlook 2000 TOOLS menu Select OPTIONS On the PREFERENCES tab Click the E-MAIL OPTIONS... button Near the bottom of thid window is a little box to turn on next to "Mark my comments with:" -- Nikki Peterson [MVP - Outlook] "Paul Barton" <paulb@NOSPAMcae.com.au> wrote in message news:%230g6OU%230DHA.2604@TK2MSFTNGP09.phx.gbl... I can't find how to turn on the reply marking (ie my name) when replying...

Custom dat & time code
Does anyone know if it's possible write a custom cell-format code that=20 would look like the one Excel provides (mm/dd hh:mm AM/PM) but sets the=20 time in red? I've tried "mm/dd [RED]hh:mm AM/PM," but whenever I save = it,=20 Excel moves the red bracket to the beginning of the code so the whole = thing=20 is red. Help! Any ideas? I don't think you can. You can't format formulas like this (character by character). And you can't format numbers this way, either. (Anyway, I couldn't do it.) Wuddus wrote: > > Does anyone know if it's possible ...

can a column have diffent width?
I have dates in column A and 20 rows down or so, I want to have names in column A and numbers in column B but column A must be wider to accept the names. I want the dates to be a certain width say 64 pixels and the names to be 336 pixels,can this be done? thanks No, a column has one width from top to bottom best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "dave" <spammer@nospammer.net> wrote in message news:kdLGf.8425$bW6.4202@bignews7.bellsouth.net... >I have dates in column A and 20 rows down or so, I want to have names in >...

How to: Open *.MSG File from Hard Drive via Code
I need to : Open *.MSG File from Hard Drive via Code and extract the file attachment. I have hundreds of these emails that we need the attachments extracted from. But the .msg files are on a hard drive. Someone help me out here.... I need the code to open and access the mailitem.... how do I open a .msg file? Use the CreateItemFromTemplate() method for that. -- Ken Slovak [MVP - Outlook] http://www.slovaktech.com Author: Professional Programming Outlook 2007. Reminder Manager, Extended Reminders, Attachment Options. http://www.slovaktech.com/products.htm "B...

Table / Column descriptions
Is there ANYWHERE that you can find what the column names in each table represent ? Such as there is a column in the SOP10200 table that is PURCHSTAT. Some of them are a 1 and some are a 2. What does this represent ? Where can we find out a detailed list of what these columns are in the tables ? Why does MS not provide this ? Is it a secret or is it just because they dont even know ? This is VERY frustrating in trying to troubleshoot Great Plains .... or shoud I say Great PAINS !! Accolade has been the best so far, just not as detailed as one would need for accurate tro...

Excel: Ignoring Specific data in a column
I have Excel data (raw) that I am importing into an Excel sheet which has my formulas). I am trying to add up a column of numbers, but some of the fields have the letter's "N/A" Presently I am using formula =sum(A10:A15000), but it is not working due to the "N/A". How can I get Excel to ignore the "N/A" and just add the cell's that do have the numbers. For reference, the numbers do have decimal points (xx.xx) Thanks, -Avi If the N/A are just text, then select the column and use find/replace to make them all zeros. If the N/A are the result of fu...

How do I copy an Excel chart with added text boxes into Word or P.
I have created a chart in Excel and added a number of text boxes on top of the chart. I need to paste the entire chart & text boxes into Word and/or Powerpoint; however by selecting the chart and copying, the text boxes are excluded. MS help suggests entering the text boxes outside the chart then moving them into the chart and grouping the chart and text boxes; however there does not seem to be the ability to group the chart with the text boxes. If you had clicked on the chart before making the textboxes, all the items would have been grouped. But you can do it later: Hold down SHI...

Multiple Column Sorting
I've been reading online about sorting multiple columns and I'm confused. Selecting one column to sort also selects all the other columns as well, at least in Excel 2000 and XP. I didn't see any difference in selecting one column or using the "Then by" boxes, in fact, the "Then by" choices didn't work. If the first column sorted in ascending order, and I chose to sort the second and third columns to sort in descending order, Excel still sorted all three columns in ascending order. If Excel sorts the all the columns together by default, why use the "Then...

Highlighting text using conditional formatting
Hi All I'm trying to highlight certain text in a list - namely all text that end with river, stream or creek. I've been trying to use conditional formatting but for some reason I can't seem to get it to go (I would have thought * wildcard would have done the trick but no such luck). Ideally you would have a different colour for rivers, creeks and streams. The list is like this Grey River Brash Creek Fred Stream Hell Stream Roger River Turd Creek and so on (about 3000 entries) Any ideas about how you can do this? Feedback appreciated. Thanks Mike Try this... Select the text ...

Column Labels #5
I have a coworker who changed the column labels from letters to numbers. She does not know how she did it. Does anyone know how to change them back to letters. The columns are numbered in all of the worksheets that she opens. Stephanie tools>options>general>settings>ric1 uncheck -- Don Guillett SalesAid Software donaldb@281.com "Stephanie" <anonymous@discussions.microsoft.com> wrote in message news:3d7701c52341$aceff2e0$a501280a@phx.gbl... > I have a coworker who changed the column labels from > letters to numbers. She does not know how she did it. Do...

Powerpoint Placeholder Text Box
I have a series of template pages with multiple text boxes. I would like all the text boxes to auto fit consistently if one needs to. It works like that on a two box that is in the basic package, but when I created a 3 box and a 4 box, they each auto fit based on the text in the individual box. I've tried grouping them in slide master, and normal view. Any ideas? ...

Search/Extract Data w/in Text File
Hello again! I am searching within a text file for the last row within the file. After I locate the row I need to separate the data and place into multiple cells on my worksheet. The code I have is as follows: Dim TextPath As String Dim DelimSrchChar As String Dim METtime As String Dim DataBuffer As Variant Dim NCData As Variant Dim DelimPos As Integer '=============================================== DelimSrchChar = "," TextPath = "C:\Temp\Formatted_NC_SN1008.txt" Open TextPath For Input As #1 'open text file for SN1008 Do While Not EOF(1) ...

summing number of 'y's in a filtered column
Basically I have a spreadsheet which is filtered by column R and I wan to sum the number of 'Y's in column P based only on the visibl filtered table rather than hidden rows. Now I know the subtotal worksheet function will ignore hidden rows however it does not seem to be able to total up the number of 'Y's i the range of cells. The count function would sum up the number of 'y' but it counts the hidden columns as far as I know. Is there some other function which would allow me to sum the number o 'y's ignoring hidden rows -- neowo ---------------------------...

display last value in a column
I would like to display the last value unequal to zero recorded in a column in a specific cell. For example, A1 is to display the last value unequal to zero recorded in column B. The number of entries and values in column B changes frequently; therefore the last value in column B is in a different row. Thanks. -- Mike Try this... Assuming there are no logical TRUE entries in the range. =LOOKUP(1E100,1/B2:B100,B2:B100) -- Biff Microsoft Excel MVP "Mike" <Mike@discussions.microsoft.com> wrote in message news:DFFCBDB0-3632-46AC-A3AB-073E6F0D475...

combobox code
i can't get the following listfillrange code to work more than once ='g:\[data.xlsx]sht'!a1:c66. after the excel file is saved and closed the listfillrange changes to [data.xlsx]sht'!a1:c66. what is causing this to happen swain.s@blueyonder.co uk;592206 Wrote: > i can't get the following listfillrange code to work more than once > ='g:\[data.xlsx]sht'!a1:c66. > > after the excel file is saved and closed the listfillrange changes to > [data.xlsx]sht'!a1:c66. > > what is causing this to happen I thought it'd be t...

Problem wih Code
I have the code below which simply just copies rows 18:21 to 9 ranges below it. My sheet is protected with the word FIELD. Everytime I run the macro I get an error "Paste Method Worksheet Class Failed", when I debug it highlights the line "ActiveSheet.Paste" - the first instance of this line. Can anyone assist? Thanks Sub CopyCells() Sheets("Report").Select ActiveSheet.Unprotect Password:="FIELD" ClearCells ActiveSheet.Unprotect Password:="FIELD" Sheets("Report").Select Rows("18:21").Select...

How do I sort data in column B to align with data in column A?
Im fairly green to Excel but what I am after is I want to be able to sort a range of codes in Column B to basically move and realign to the same row as the data that is in Colmn A eg Start Column A Column B R F G B B H H R F G Finish Column A Column B R R G G B B H H F F Piece a cake. Delete columm B, then copy column A and paste into Column B "Jephri1" wrote: > Im fairly green to Excel but what I am after is I...

Text to Columns #4
I have a list of the Fortune 1000 companies in one cell. Is there a way to convert this information into columns with: Company City State Telephone 1 Wal-Mart Stores, Inc. Bentonville, AR 479-273-4000 2 Exxon Mobil Corporation Irving, TX 972-444-1000 3 General Motors Corporation Detroit, MI 313-556-5000 4 Ford Motor Company Dearborn, MI 800-392-3673 5 General Electric Company Fairfield, CT 203-373-2211 6 ChevronTexaco Corporation San Ramon, CA 925-842-1000 7 ConocoPhillips Houston, TX 281-293-1000 8 Citigroup Inc New York, NY 212-559-1000 9 International Business Machines Corporatio...

How to increase number of columns appear in a worksheet?
You only get 256 columns with any released version of excel. Dhinesh wrote: -- Dave Peterson ...

How do I sort a column consisting of fractions, decimals, and who.
I need to sort products by the horsepower. Horsepower can be a fraction, decimal or whole number. For example, I need 1/100 to come before 1/10, 1/125 to come before 1/12, 1/10 to come before 1.0, 3/4 to come before 1...etc. Why don't you use the Fraction format under Number format? That way you only enter the decimal value and it will be represented by it's fraction, but the sorter won't be confused. Another option is to use this formula: =IF(ISERR(SEARCH("/",E8)),VALUE(E8),LEFT(E8,SEARCH("/",E8)-1)/RIGHT(E8,SEARCH("/",E8)-1)) Where E8 is ...

Worksheet 'shape' (#columns by #rows)
Hi All; Have a problem here in that we would like to import some data into a spreadsheet, but the data array is 512 by 512 elements. Excel seems to force a limit of 256 columns. Is there a way to change the array shape? In any Excel version 97-2003. -- __ Pete Snell Royal Military College Kingston Ontario Snell-p@rmc.ca The reasonable man adapts himself to the world; the unreasonable one persists in trying to adapt the world to himself. Therefore all progress depends on the unreasonable man. - George Bernard Shaw Hi Peter there is now way to change this limitation (Ex...

FRx Book Code/Attribute Category
In the Column Headings should be able to use a code that picks up the Book Code so one knows what Budget ID the column refers to. This is because it is hardcoded into the report and someone may have changed in Designer and another user may be unaware that the Budget info displayed relates to a prior year etc. Or another alternative would be to specify the Budget ID at run time. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If ...

Text between Worksheets?
I've set up a budget workbook with 12 worksheets, one for each month. The first sheet contains the list of budget items. If I change the name of a budget item on the first worksheet, is there a way (or formula) to have this text automatically change on the other 11 worksheets? This would preclude my having to make the change individually on each of the other 11 sheets. How would I do this? Thanks! Select sheet2 then SHIFT + click on sheet12. In A1 enter =sheet1!A1 Drag/copy down as far as you need. The A1 will increment as you copy down. Select sheet1. Change A1 data and...

How can I hide check box created via FORMS together with column?
Hey guys, how can I hide a check box created using FORMS together with column it is placed in (or in some other way). "Move and size with cell" option is not active in object positioning for check boxes created via FORMS as opposed to those created via CONTROL TOOLBOX. Thanks, Max I think you'll have better luck (pronounced easier time!) with the checkboxes from the Control toolbox toolbar. But maybe you could have a macro that hides the columns/rows and also looks at the objects to see if they should be .visible = false. And the same kind of macro to make the checkbox&#...