Excel For Mac Text To Columnsexfasr

1. Mac Text Editor
2. Text To Columns In Excel Mac

Making and Using Excel web queries

Web queries bring tables from the web directly into Microsoft Excel. These instructions explain how to make and use web queries for Microsoft Excel on your Mac.

Our free VBA Add-in installs directly into the VBA Editor, giving you access to 150 ready-to-use VBA code examples for Excel. Simply click your desired code example and it will immediately insert into the VBA code editor. If you move the linked Excel file (for example, to another folder), the link will break. To relink it, follow the steps above again. To edit the data, double-click the table to open the linked worksheet in Excel. If you edit the worksheet in Excel, the changes appear in the Word document when you save the Excel worksheet.

Data in Web pages

Data in web pages can be displayed a variety of ways:

• In row and column tables that are HTML based
  
• As the result of a query
  
• As a picture
• Within a PDF file
  

Getting the data

Free samples - but now they're broken!

Excel comes with these three free sample web queries that were supposed to let you see web queries in action: Dow Jones put the kabash on these samples, so they no longer work.

• MSN MoneyCentral Currencies
  
• MSN MoneyCentral Major Indices
• MSN MoneyCentral Stock Quotes

See below ffor how to make your own queries. Once you've made a web query, follow these instructions to run it.

Run a web query

Your Mac must have a live internet connection to run queries on web pages that are found on the internet.

There are three ways to run a web query in Excel 2011. Excel comes with some example web queries. To try one of the example queries, run the MSN MoneyCentral Currencies query using the first method described below. The result set will be a data range containing up to the minute currency exchange rates for the US dollar vs other currencies. The query becomes part of the Excel worksheet. The result set of a query is called a QueryTable.

• From the Data menu choose Get External Data > Run Saved query.
  
1. The Choose a Query dialog displays defaulting to the Queries folder.
2. Select a query file to run in the Choose a Query dialog.
3. Click the Get Data button.
4. The Returning Data to Excel dialog opens. You can choose a cell that will be used for the upper left cell of the imported table. The default location shown is the cell that was selected when you started these steps. We'll discuss the Properties option later.
5. Click the OK button. Wait as Excel imports the data from the web page. Import speed depends on network speed and how much data you import.
   
• Click the Run Saved Query button. Because Microsoft forgot to put this button into the Ribbon, to use this option you must first add the Run Saved Query command to a toolbar Once you've added the Run Saved Query command to a toolbar:
1. Click the Run Saved Query button.
2. The Choose a Query dialog displays defaulting to the Queries folder.
3. Select the query file to run in the Choose a Query dialog.
4. Click the Get Data Button.
5. The Returning Data to Excel dialog opens. You can choose a cell that will be used for the upper left cell of the imported table. The default location shown is the cell that was selected when you started these steps. We'll discuss the Properties option later.
6. Click the OK button. Wait as Excel imports the data from the web page. Import speed depends on network speed and how much data you import.
• Run a query using a VBA (Visual Basic for Applications) macro
• Click here for instructions on how to display the Visual Basic Editor (VBE)
• Click here for an introduction to how to code using the VBE
• For an example, see heading below on this page: Web queries and Visual Basic for Applications (VBA)
  

To refresh the data from the web page, select any cell within the query results and then from the Data menu choose Refresh Data.

Web query results are displayed differently in Excel from the way the look on a web page. Formatting is minimally retained. Hyperlinks may or may not work. 'Relative' hyperlinks and specialty hyperlink protocols will not work when clicked.

Make a simple web query

The simplest web query consists of the URL of a web page which has an HTML table saved as a text file. To make your web query, take the following steps:

1. Open Microsoft Word to a new, blank document
2. Type or paste the URL of the web page that has an HTML table into your document. In this example use http://www.agentjim.com/. When pasting a URL, use the little widget that appears and Keep Text Only from the pop-up menu.
3. Use File > Save As and choose file format Plain Text (.txt). Give the text file a sensible name. Navigate to this location: Applications:Microsoft Office 2011:Office:Queries and then click the Save button.
4. The File Conversion dialog opens. In the options for Text Encoding choose MS-DOS, and in the Options section click the check box for Insert Line Breaks. Then click the OK button to complete saving your file.
5. We're done with Word. If you want to he a purist, Take this optional step: in Finder, navigate to the file you just saved. Change the file extension from .txt to .iqy. You have to do this in Finder because Word won't let you change the file extension form .txt to .iqy when you save the file. The web query file will work even if you don't change the file extension to .iqy and leave it as .txt.
   

Now you can run your saved web query as described earlier.

Make a dynamic (parameter) web query

Some web pages request input so that custom result sets can be delivered. You can see how such a web query is constructed by opening the files in Microsoft Word to examine the contents of the built-in web queries. When you run the web query MSN MoneyCentral Stock Quotes you are prompted to enter the code for a particular stock. If you use Microsoft Word to open the sample web query MSN MoneyCentral Stock Quotes you can see the text for that query. This query uses the formal structure for a web query. The question mark embedded after the URL triggers Excel to display a dialog box prompt for the web page, which is expecting a stock market SYMBOL parameter from a web browser. QUOTE is the name of the parameter (See Figure 3 below):

WEB

1

http://moneycentral.msn.com/investor/external/excel/quotes.asp?SYMBOL=['QUOTE','Enterstock, fund or other MSN MoneyCentral Investor symbols separated by commas.']

Selection=EntirePage

Our free VBA Add-in installs directly into the VBA Editor, giving you access to 150 ready-to-use VBA code examples for Excel. Simply click your desired code example and it will immediately insert into the VBA code editor. If you move the linked Excel file (for example, to another folder), the link will break. To relink it, follow the steps above again. To edit the data, double-click the table to open the linked worksheet in Excel. If you edit the worksheet in Excel, the changes appear in the Word document when you save the Excel worksheet.

Data in Web pages

Data in web pages can be displayed a variety of ways:

• In row and column tables that are HTML based
  
• As the result of a query
  
• As a picture
• Within a PDF file
  

Getting the data

Free samples - but now they're broken!

Excel comes with these three free sample web queries that were supposed to let you see web queries in action: Dow Jones put the kabash on these samples, so they no longer work.

• MSN MoneyCentral Currencies
  
• MSN MoneyCentral Major Indices
• MSN MoneyCentral Stock Quotes

See below ffor how to make your own queries. Once you've made a web query, follow these instructions to run it.

Run a web query

Your Mac must have a live internet connection to run queries on web pages that are found on the internet.

There are three ways to run a web query in Excel 2011. Excel comes with some example web queries. To try one of the example queries, run the MSN MoneyCentral Currencies query using the first method described below. The result set will be a data range containing up to the minute currency exchange rates for the US dollar vs other currencies. The query becomes part of the Excel worksheet. The result set of a query is called a QueryTable.

• From the Data menu choose Get External Data > Run Saved query.
  
1. The Choose a Query dialog displays defaulting to the Queries folder.
2. Select a query file to run in the Choose a Query dialog.
3. Click the Get Data button.
4. The Returning Data to Excel dialog opens. You can choose a cell that will be used for the upper left cell of the imported table. The default location shown is the cell that was selected when you started these steps. We'll discuss the Properties option later.
5. Click the OK button. Wait as Excel imports the data from the web page. Import speed depends on network speed and how much data you import.
   
• Click the Run Saved Query button. Because Microsoft forgot to put this button into the Ribbon, to use this option you must first add the Run Saved Query command to a toolbar Once you've added the Run Saved Query command to a toolbar:
1. Click the Run Saved Query button.
2. The Choose a Query dialog displays defaulting to the Queries folder.
3. Select the query file to run in the Choose a Query dialog.
4. Click the Get Data Button.
5. The Returning Data to Excel dialog opens. You can choose a cell that will be used for the upper left cell of the imported table. The default location shown is the cell that was selected when you started these steps. We'll discuss the Properties option later.
6. Click the OK button. Wait as Excel imports the data from the web page. Import speed depends on network speed and how much data you import.
• Run a query using a VBA (Visual Basic for Applications) macro
• Click here for instructions on how to display the Visual Basic Editor (VBE)
• Click here for an introduction to how to code using the VBE
• For an example, see heading below on this page: Web queries and Visual Basic for Applications (VBA)
  

To refresh the data from the web page, select any cell within the query results and then from the Data menu choose Refresh Data.

Web query results are displayed differently in Excel from the way the look on a web page. Formatting is minimally retained. Hyperlinks may or may not work. 'Relative' hyperlinks and specialty hyperlink protocols will not work when clicked.

Make a simple web query

The simplest web query consists of the URL of a web page which has an HTML table saved as a text file. To make your web query, take the following steps:

1. Open Microsoft Word to a new, blank document
2. Type or paste the URL of the web page that has an HTML table into your document. In this example use http://www.agentjim.com/. When pasting a URL, use the little widget that appears and Keep Text Only from the pop-up menu.
3. Use File > Save As and choose file format Plain Text (.txt). Give the text file a sensible name. Navigate to this location: Applications:Microsoft Office 2011:Office:Queries and then click the Save button.
4. The File Conversion dialog opens. In the options for Text Encoding choose MS-DOS, and in the Options section click the check box for Insert Line Breaks. Then click the OK button to complete saving your file.
5. We're done with Word. If you want to he a purist, Take this optional step: in Finder, navigate to the file you just saved. Change the file extension from .txt to .iqy. You have to do this in Finder because Word won't let you change the file extension form .txt to .iqy when you save the file. The web query file will work even if you don't change the file extension to .iqy and leave it as .txt.
   

Now you can run your saved web query as described earlier.

Make a dynamic (parameter) web query

Some web pages request input so that custom result sets can be delivered. You can see how such a web query is constructed by opening the files in Microsoft Word to examine the contents of the built-in web queries. When you run the web query MSN MoneyCentral Stock Quotes you are prompted to enter the code for a particular stock. If you use Microsoft Word to open the sample web query MSN MoneyCentral Stock Quotes you can see the text for that query. This query uses the formal structure for a web query. The question mark embedded after the URL triggers Excel to display a dialog box prompt for the web page, which is expecting a stock market SYMBOL parameter from a web browser. QUOTE is the name of the parameter (See Figure 3 below):

WEB

1

http://moneycentral.msn.com/investor/external/excel/quotes.asp?SYMBOL=['QUOTE','Enterstock, fund or other MSN MoneyCentral Investor symbols separated by commas.']

Selection=EntirePage

Formatting=All

PreFormattedTextToColumns=True

ConsecutiveDelimitersAsOne=True

SingleBlockTextImport=False

Here's another example of a dynamic web query. This simple is for Yahoo Finance where jasz is a stock market symbol:

http://www.finance.yahoo.com/q?f=jasx

This example also queries Yahoo, but this one puts the results into a single column

Working with query result sets

The result set querytable of a web query has many properties you can control. When you first make your web query, there are settings available to you in the Returning External Data to Excel dialog (see Figure 1).

Figure 1 - Returning External Data to Excel

NCAA Football 2001, released only for the PlayStation video game console, was the 8th installment of the NCAA Football Series. It was created by EA Sports and released on July 25th, 2000. Ncaa football 2001. The 2001 NCAA Division I-A football season was the first college football season of the 21st Century. It ended with the University of Miami winning the national title for the fifth time. 2001 College Football Year Summary. 2000 CFB Year Summary 2002 CFB Year Summary. Champions: Miami (FL) (AP, BCS, FWAA, NFF, USA-ESPN). Primary NCAA Data Provided By. Despite the tweaked graphics and artificial intelligence, NCAA College Football 2001 from EA Sports is the same old college football game on the PlayStation. The third incarnation in the series, this game straddles the fence between a realistic simulation and a fast-paced action game.

Click the Properties button to display the External Data Range Properties dialog when you first make a query. You can also display this dialog by right-clicking into the querytable result set and choosing Data Range Properties from the pop-up menu. The options presented are context sensitive. Not all options are available for web queries.

Figure 2 - External Data Range Properties

If your web query is dynamic (requests a parameter), the Parameters button becomes available in the Return External Data to Excel dialog box. Click the Parameters button to display the Parameters dialog, (see Figure 2). In this dialog you can modify the text of the prompt, enter a value to always use, or choose a cell and use that cell's value to satisfy the prompt. The Parameters dialog can also be displayed by right-clicking in the querytable result set of a parameter query and choosing Parameters from the pop-up menu.

Note the option to 'Fill down formulas in columns adjacent to data.' Enabling this option lets you use calculated columns with the web query result set.

Figure 3 - Parameters

Web queries and Visual Basic for Applications (VBA)

You must use full URLs and they must be http:// type URLs. Here is a code example showing VBA syntax:

Sub MakeWebQuery()
With ActiveSheet.QueryTables.Add(Connection:= _
'URL;http://www.domainname.com', Destination:= _
Range('A1'))
.PostText = 'local'
.Name = False
.FieldNames = False
.RefreshStyle = xlInsertDeleteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.HasAutoFormat = True
.RefreshOnFileOpen = 1
.BackgroundQuery = False
.TablesOnlyFromHTML = True
.SaveData = True
.Refresh BackgroundQuery:=False
.UseListObject = False
End With
End Sub

Use the following code to refresh a query. The selection cursor must be in the QueryTable before running this code. If a parameter query is refreshed, the user will be prompted for parameters.

Sub RefreshQuery()
Selection.QueryTable.Refresh BackgroundQuery:=False
End Sub

By default, Excel has a certain row height and column width, and when you enter anything that occupies more space than the current size of size, you'll see that it spills out (as shown below).

In such cases, you can adjust the rows and columns to fit the text in the cell (so that the text is completely within the cell).

Contact important infomrs. mac's classes near. This can be done using the AutoFit feature in Excel.

In this tutorial, I will show you how to use Autofit in Excel using multiple ways. I will also cover some other Excel features you can use when you're working with text data in Excel.

What is Excel AutoFit?

When you enter anything in a cell in Excel, you would notice that it spills when the size of the text/data is more than the size of the column.

AutoFit is a feature in Excel that allows you to quickly adjust the row height or column width to fit the text completely (so that there is no spilling over to other cells).

Also, AutoFit means that you don't have to manually specify the column width or row height (or manually drag and adjust the column width). It's Auto- i.e., it will figure out itself how much it should expand/contract to fit the current cell content

• AutoFit Column Width: This feature automatically adjusts the column width to fit the text in the cell. You can fit text for multiple columns at once (as we will see later in examples)
• Autofit Row Height: This feature automatically adjusts the row height to fit the text in the cell. You can autofit multiple rows at once.

In most cases, you would notice that Excel automatically adjusts the row height when you enter more than one line in the same cell. In those cases, you won't need to do any row height adjustment.

But sometimes, you may get a dataset from someone or as a download, where row height has already been fixed and needs adjustment. You can use the ‘AutoFit Row Height' feature in that case.

How to AutoFit in Excel (Adjust Rows and Columns to Fit Text)

There are a couple of ways to Autofit in Excel:

1. Using the mouse double-click method
2. Using the AutoFit option in the ribbon
3. Using a keyboard shortcut

All these methods work fine and you can choose whichever works best for you.

In most cases, I use the mouse double-click method as I find it the most convenient.

AutoFit Rows and Columns Using Mouse Double-Click

Suppose you have a dataset as shown below where the text spills to the other column as the column width is less.

Here is how you can autofit column width using the mouse:

1. Hover the cursor at the right edge of the column header
2. You would notice that the cursor changes to a double-sided arrow icon
3. Double-click the left key on your mouse (or the trackpad)

That's it!

You would see that the column width changes automatically to adjust all the cells so that there is no overflowing of text to other cells. Note that the column width is expanded to adjust the cell which needs the maximum width.

You can also use the double-click method to adjust multiple columns at once.

Suppose you have the dataset as shown below where there are two columns and both have text that needs to autofit.

You can use the below steps to autofit column width using the mouse:

1. Select all the columns that need to be autofitted, In this case, it would be column A and B.
2. With both the columns selected, place the cursor at the right edge of the column header
3. You would notice that the cursor changes to a double-sided arrow icon
4. Double-click the left key on your mouse (or trackpad)

As soon as you do this, it will autofit both the selected columns (as shown below)

Note that for this to work, you need to select the entire columns and not the cells in the worksheet.

AutoFit Rows and Columns Using Excel Ribbon

Another quick way to autofit cells in Excel is by using the option in the ribbon.

Suppose you have a dataset as shown below where you want to autofit Column A.

Below are the steps to do this:

1. Select the column you need to autofit
2. Click the Home tab
3. In the Cells group, click on the ‘Format' option. This will show additional options in the drop-down
4. Click on the ‘Autofit Column Width' option.

The above steps would instantly expand the column to adjust the text in the cells.

You can also use the same steps to also autofit row height. To do that you first need to select the rows that you need to autofit and then select the ‘Autofit Row Height' option.

AutoFit Rows and Columns Using a Keyboard Shortcut

If you're not a fan of using the mouse and would prefer a keyboard shortcut instead, this method is for you.

Below are the keyboard shortcuts that will autofit the cells:

To Autofill column width:

Mac Text Editor

To Autofill row height:

Here is how to use these keyboard shortcuts:

1. Select the row/column that you want to autofit
2. Use the keyboard shortcut with keys in succession. For example, if you're using the shortcut ALT + H + O + I, press the ALT key, then the H key, and so on (in succession).

AutoFit not Working (Possible Solutions)

While Autofit works well in most cases, sometimes, you may find that this feature doesn't work.

There could be many reasons for it not working, and in many cases, it sometimes works and sometimes doesn't.

Here I am listing some possible reasons why autofit may not be working on your Excel:

When there are merged cells

Text To Columns In Excel Mac

Sometimes, Autofit refuses to work when there are merged cells in your Excel. This is a known issue if you're using really old versions of Excel (2003 or prior versions).

Microsoft has even acknowledged this issue here. If this happens to you, the only workaround is to manually set the row height or column width.

When you have applied wrap text

When you have applied text wrap to a cell and you try and autofit the row, you may find that there are some extra spaces at the bottom or top of the row.

These do not space characters. This is just a space that exists for no reason. Sometimes, changing the column width makes this go away. And there is always an option to manually resize the row (or right-click and use the row-height option to specify the row height value)

Another place where autofit may not work is when you have applied wrap text to cell and you try to autofit the column. This is less of autofit not working and more by design. Since you have applied wrap text, Excel doesn't know what is the column width it should use when you autofit.

After all, wrap text means that any text would be wrapped in such a way that it would remain within the cell. In such a case, trying to autofit the column won't do anything. You can, however, adjust the column width manually.

Alternatives to AutoFit Option in Excel

While autofit is what you would want in most cases, there some other options as well that can help you present your data in a better way (especially in cases where autofit is not the way to go).

Wrap Text

If you have a cell that has a lot of text, autofitting the column will expand it and make the column huge.

This may not be the right way to present your data, and it may make more sense to have a bigger row and narrower column.

When you apply wrap text to a cell, it makes the content of the cell remain within the cell. This allows you to choose what column width you want and then you can autofit the row height to match the column width.

Or vice-versa…

Adjust the row height to what you want and then you can use auto-fit to adjust the column width to make sure text is all the cell content is visible and within the cell.

Shrink to Fit

Another option you can use is Shrink to Fit.

Unlike Autofit, which adjusts the column width or row height, when you apply ‘Shrink to Fit' on any cell, it would simply change the cell font to fit everything in the given column width.

For example, if you have some text in a cell and it spills to other cells, when you apply to Shrink to Fit on this cell, the font of the cell would be lowered to fit the cell content in the existing column width.

Below are the steps to apply the ‘Shrink to Fit' format to a cell:

1. Select the cells to which you want to apply ‘Shrink to Fit'
2. Hold the Control key and press the 1 key (this will open the Format Cells dialog box)
3. Click the ‘Alignment' tab
4. In the ‘Text Control' options, check the ‘Shrink to Fit' option

Note that Shrink to Text doesn't work for cells that have the ‘Wrap Text' applied to it.

So these are some of the ways you can use Autofit rows or columns in Excel. I have also covered some possible reasons why autofit may not work on your system and some alternatives to it in Excel.

I hope you found this tutorial useful!

You may also like the following Excel tutorials:

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster