-->
Note
Office 365 ProPlus is being renamed to Microsoft 365 Apps for enterprise. For more information about this change, read this blog post.
Mar 29, 2013 Question: Q: Convert Windows Excel File into a Mac Excel File I have transferred my Workout Journal spread sheet over from my girlfriends PC (which I use often). Problem is, I can't access it the file on my Mac in Excel or Numbers. Oct 08, 2019 Convert a Numbers spreadsheet in Numbers for iCloud. Sign in to iCloud.com with your Apple ID. Click Numbers. In the spreadsheet manager, click the More button on the file you want to convert, then choose Download a Copy. If you have the spreadsheet open. Choose a format for the spreadsheet.
Summary
This article contains and describes formulas that calculate the following:
- The number of occurrences of a text string in a range of cells.
- The number of occurrences of a character in one cell.
- The number of occurrences of a character in a range of cells.
- The number of words (or text strings) separated by a character in a cell.
More Information
Formula to Count the Number of Occurrences of a Text String in a Range
=SUM(LEN(range)-LEN(SUBSTITUTE(range,'text',')))/LEN('text')
Where range is the cell range in question and 'text' is replaced by the specific text string that you want to count.
Note
The above formula must be entered as an array formula. To enter a formula as an array in Excel for Windows, press CTRL+SHIFT+ENTER. To enter a formula as an array in Excel for Macintosh, press COMMAND+RETURN.
The formula must be divided by the length of the text string because the sum of the character length of the range is decreased by a multiple of each occurrence of the text string. This formula can replace all later formulas in this article except the formula to count the number of words in a cell.
Example 1: Counting the Number of Occurrences of a Text String in a Range
Start Excel, and then open a new workbook.
Type the following on sheet1:
The value of cell A8 is 4 because the text 'apple' appears four times in the range.
Formula to Count the Number of Occurrences of a Single Character in One Cell
=LEN(cell_ref)-LEN(SUBSTITUTE(cell_ref,'a','))
Number Conversion
Where cell_ref is the cell reference, and 'a' is replaced by the character you want to count.
Note
This formula does not need to be entered as an array formula.
Example 2: Counting the Number of Occurrences of a Character in One Cell
Use the same data from the preceding example; assuming you want to count the number of occurrences of the character 'p' in A7. Type the following formula in cell A9:
A9: =LEN(A7)-LEN(SUBSTITUTE(A7,'p','))
The value of cell A9 is 3 because the character 'p' appears three times in A7.
Formula to Count the Number of Occurrences of a Single Character in a Range
=SUM(LEN(range)-LEN(SUBSTITUTE(range,'a',')))
Where range is the cell range in question, and 'a' is replaced by the character you want to count.
Note
The above formula must be entered as an array formula. To enter a formula as an array formula in Excel, press CTRL+SHIFT+ENTER.
Example 3: Counting the Number of Occurrences of a Character in a Range
Use the same data from the preceding example; assuming you want to count the number of occurrences or the character 'p' in A2:A7. Type the following formula in cell A10:
A10: =SUM(LEN(A2:A7)-LEN(SUBSTITUTE(A2:A7,'p',')))
Note
The above formula must be entered as an array formula. To enter a formula as an array formula in Excel, press CTRL+SHIFT+ENTER.
The value of cell A10 is 11 because the character 'p' appears 11 times in A2:A7.
Formula to Count the Number of Words Separated by a Character in a Cell
=IF(LEN(TRIM(cell_ref))=0,0,LEN(cell_ref)-LEN(SUBSTITUTE(cell_ref,char,'))+1)
Where cell_ref is the cell reference, and char is the character separating the words.
Note
There are no spaces in the above formula; multiple lines are used only to fit the formula into this document. Do not include any spaces when you type it into the cell. This formula does not need to be entered as an array formula.
Example 4: Counting the Number of Words Separated by a Space in a Cell
To count the number of words in a cell where the words are separated by a space character, follow these steps:
Start Excel, and then open a new workbook.
Type the following on sheet1:
The formula in cell A2 returns a value of 4 to reflect that the string contains four words separated by spaces. If words are separated by multiple spaces or if words start or end in a space, it does not matter. The TRIM function removes extra space characters and starting and ending space characters in the text of the cell.
In Excel, you can also use a macro to count the occurrences of a specific character in a cell, or range of cells.
References
For additional information about counting occurrences of text, click the following article number to view the article in the Microsoft Knowledge Base:
89794 How to use Visual Basic for Applications to count the occurrences of a character in a selection in Excel
You can convert and open spreadsheets in Numbers on iPhone, iPad, iPod touch, Mac, or online at iCloud.com.
Convert and open spreadsheets in Numbers on iPhone or iPad
You can work with a variety of file formats in Numbers on your iPhone or iPad. Check which formats are compatible with Numbers on iPhone and iPad.
Convert a Numbers spreadsheet in Numbers on iPhone or iPad
If you want to open a Numbers spreadsheet in another app like Microsoft Excel, use Numbers to convert the spreadsheet into the appropriate format.
- In Numbers, open the spreadsheet that you want to convert, then tap the More button .
- Tap Export.
- Choose a format for your spreadsheet. For example, if you want to open your Numbers spreadsheet in Microsoft Excel, choose Excel.
- Choose how you want to send your spreadsheet, like with Mail or Messages.
Open a file in Numbers on your iPhone or iPad
To open a file like a Microsoft Excel spreadsheet on your iPhone or iPad, tap the file in the spreadsheet manager. If you don’t see the spreadsheet manager, tap the Back button (on an iPhone or iPod touch) or Spreadsheets (on an iPad), then tap the file that you want to open. If a file is dimmed, it’s not in a compatible format.
You can also open a file in Numbers from a different app like the Files app, or from an email:
- Open the other app, then select the spreadsheet or attachment.
- Tap the Share button .
- Tap Copy to Numbers. The original file remains intact.
After the file opens, you might get a message that the file was last edited in an app other than the most recent version of Numbers. Tap Done to open the file in Numbers.
Convert and open spreadsheets in Numbers for Mac
You can work with a variety of file formats in Numbers for Mac. Check which formats are compatible with Numbers for Mac.
Convert a Numbers spreadsheet in Numbers for Mac
If you want to open a Numbers spreadsheet in another app like Microsoft Excel, use Numbers to convert the spreadsheet to an appropriate format.
- In Numbers, open the Numbers spreadsheet that you want to convert.
- Choose File > Export To, then select the format.
- In the window that appears, you can choose a different format or set up any additional options. For example, you can require a password to open an exported PDF, or choose the format for an exported Excel spreadsheet.
- Click Next.
- Enter a name for your file and select a folder to save it to.
- Click Export.
To send a file in a specific format through Mail, Messages, AirDrop, or Notes, choose Share > Send a Copy, choose how you want to send the spreadsheet, then choose a format.
Open a file in Numbers for Mac
You can open a file from the Finder or from the Numbers app:
- From the Finder, Control-click the file, then choose Open With > Numbers. If Numbers is the only spreadsheet app on your Mac, you can just double-click the file.
- From the Numbers for Mac app, choose File > Open, select the file, then click Open. If a file is dimmed, it’s not a compatible format.
When the file opens, you might get a message that the spreadsheet will look different. For example, Numbers notifies you when fonts are missing. You might see also see warnings when you open spreadsheets created in older versions of Numbers.
Convert and open spreadsheets in Numbers for iCloud
Numbers for iCloud lets you open, edit, and share your files from a supported browser on a Mac or PC. Check which formats are compatible with Numbers for iCloud.
Convert a Numbers spreadsheet in Numbers for iCloud
- Sign in to iCloud.com with your Apple ID.
- Click Numbers.
- In the spreadsheet manager, click the More button on the file you want to convert, then choose Download a Copy. If you have the spreadsheet open, click the Tools button in the toolbar, then choose Download a Copy.
- Choose a format for the spreadsheet. The file begins to download to your browser’s download location.
Open a file in Numbers for iCloud
- Sign in to iCloud.com with your Apple ID.
- Click Numbers.
- Drag the file you want to upload into the spreadsheet manager in your browser. Or click the Upload button , select the file, then click Choose.
- Double-click the file in the spreadsheet manager.
Convert Numbers File To Excel
Check file format compatibility
Formats you can open in Numbers
You can open these file formats in Numbers on iPhone, iPad, Mac, and online at iCloud.com:
- All Numbers versions
- Microsoft Excel: Office Open XML (.xlsx) and Office 97 or later (.xls)
- Comma Separated Values (.csv)
- Tab-delimited or fixed-width text files
Number Excel
Formats you can convert Numbers spreadsheets into
Numbers on iPhone or iPad:
- Microsoft Excel Office Open XML (.xlsx)
- Comma Separated Values (.csv)
- Tab Separated Values (.tsv)
Numbers for Mac:
- Numbers ’09
- Microsoft Excel: Office Open XML (.xlsx) and Office 97 or later (.xls)
- Comma Separated Values (.csv)
- Tab Separated Values (.tsv)
Convert Microsoft Excel To Mac Numbers
Numbers for iCloud:
- Microsoft Excel Office Open XML (.xlsx)
- Comma Separated Values (.csv)
Convert Excel To Mac Numbers
Learn more
Convert Microsoft Excel To Mac Numbers
- Numbers on iPhone, iPad, Mac, and online at iCloud.com use the same file format. If you create or edit a spreadsheet in Numbers on one platform, you can open it in Numbers on any of the other platforms.
- You can update to current versions of Numbers on your iPhone, iPad, or iPod touch from the App Store. To update Numbers for Mac, use the App Store app on your Mac.
- If you convert a Numbers spreadsheet into Numbers ’09-compatible spreadsheet, learn more about changes that happen when you export to iWork ’09.