Find And Replace Text In Excel For Mac

Active1 year, 10 months ago

• Find And Replace Text In Excel Macro
• Find And Replace With Excel

I've imported some data into Excel (from a text file) and it contains some sort of newline characters. It looks like this initially:

If I hit F2 (to edit) then Enter (to save changes) on each of the cells with a newline (without actually editing anything), Excel automatically changes the layout to look like this:

1. Replace & Find: Replaces the selected match with the replacement text and moves to the next. Replace: Replaces the selected match with the replacement text but doesn’t move to the next match. Previous and Next arrows: Move to the previous or next match without making a change.
2. Click the “Replace” tab in the left column and fill in the two blank spaces. In this case we shall enter “this” in the “find” field and “the” in the “and replace with” field. Enable “batch replace” and click the “Apply” button and you will be off and running.
3. Join Curt Frye for an in-depth discussion in this video, Locating and changing data using Find and Replace, part of Office 365 for Mac: Excel Essential Training.

I don't want these newlines characters here, as it messes up data processing further down the track. How can I do a search for these to detect more of them? The usual search function doesn't accept an enter character as a search character.

Highly IrregularHighly Irregular

Find And Replace Text In Excel Macro

1,4031616 gold badges3333 silver badges4949 bronze badges

I have a sheet in and Excel file with a lot of data in it. When I try to search for a word in this particular sheet I get: 'Microsoft Excel cannot find the data you're searching for' even for words that are present in the sheet.

4 Answers

In Excel the standard line break Alt + Enter character is ASCII 10. From the look of your screenshot it appears there is another character, probably ASCII 13.

To find these, in the standard Find (or Replace) dialog, in the Find What field, use the standard method of entering ASCII character codes: hold down Alt and type (on the numeric keypad) the character code, i.e., Alt 0010 for just the line break, or Alt 0013 (release Alt and press again) Alt 0010

If that extra character is not a ASCII 13, you can use the function =Code(AString) to get the code of the left most character of a string.

Louis

12.6k2929 gold badges109109 silver badges179179 bronze badges

chris neilsenchris neilsen

If you press Ctrl + J while in the Find dialog, you will be able to search for those characters.

Louis

12.6k2929 gold badges109109 silver badges179179 bronze badges

WasabiWasabi

If you want to do this with a formula, it would be FIND(A3,CHAR(10),...

This is useful if trying to break apart an address block.

jonsca

3,7241414 gold badges3030 silver badges4545 bronze badges

Dan TroxellDan Troxell

The mid() function can parse through multiple lines.

Let's say this address is in cell A1:

Let's grab the street address on the second line.
The first step is determine the position number of the two return characters.
The first return character is found at '13' with this:

The second return character is found at '36' with this:

Now the second step. We need to return our address text between the two character counts 13 & 36.

Here the formula is nested together:

Brad SmithBrad Smith

Not the answer you're looking for? Browse other questions tagged microsoft-excelworksheet-functiondata-transferdata-validation or ask your own question.

Find And Replace With Excel

From WindowIndia:

Excel Find and Replace Professional are very effective software which allows the users to find and replace in excel multiple words. This find and replace software can be used in different versions of Microsoft Excel. It can be used in MS Excel 2000, 2003, 2007, 2010 files. The software can be used by users to bold, italic; underline and even strike out any text or word in a Microsoft excel file with the replacement utility of the tool. Therefore it is highly recognised as an effective search and replace software.
The users can very easily search and replace in excel any text or document which it may appear to be inappropriate. Users can easily find and replace multiple words and can even highlight the cells of the excel sheet with different type of formatting and criteria. The latest version of this software is extremely useful. The software has many bugs fixed to it and many new features have been incorporated in it to simply the search and replace of words. Some of these features are built-in colour picker and Unicode support which has made formatting extremely easy, user-friendly and systematic.
The find and replace software can be applied to thousands of excel files like XLS, XLSX, XLSM in completely no time. The batch find and replace utility can be used very easily to find and replace words with wildcard utility in almost all kinds of excel files. This helps the users to save a considerable amount of time. It also allows the users to highlight each word in different formats. The most beneficial aspect of this software is that it can be applied to files in almost all different languages like German, French, Japanese, Chinese, French, Italian, Arabic, Greek, Norwegian, and Indonesian and may other Indian languages.
Different characters like lines, paragraphs, tab, can be processed by this software. The processing is done in a step wise manner. It process in the same folder or in different folders. It is extremely reliable, fast and accurate software.

What do you need to know about free software?