 |
|
| TheExcelAddict.com | |
| October 8, 2020 |
|
| Hi fellow Excel Addict, | |
Well, I'm back home and now in quarantine. I live in Newfoundland where COVID is almost non-existent and I just returned from visiting my daughter, granddaughter and family in Alberta where COVID is more widespread. Our provincial government has done an amazing job of keeping COVID under control, primarily by allowing our province's Chief Medical Officer call the shots and put into place the measures needed to keep our citizens safe. One of those measures is asking people to quarantine for 14 days if they have traveled outside the 'Atlantic Provinces Bubble'. The four Canadian Atlantic Provinces (Newfoundland, Nova Scotia, New Brunswick and Prince Edward Island) have all kept COVID cases extremely low and have allowed their citizens to travel within the region without the quarantine requirement. But since I traveled outside the 'bubble', I now have to quarantine for 14 days. Yes, it is extreme and yes it is a restriction on my freedom but I believe the greater good of all citizens is more important. And I JUST HAD TO go see my daughter and granddaughter in person.  When my quarantine is over I will travel around my province with little concern of contracting (or spreading) the disease. That's the kind of freedom I like. Not many people can say, as I can, that "I personally don't know anyone who has been affected by COVID". Please, please, please, STAY SAFE!! I hope you enjoy today's tip. Please share it on social media and with your colleagues and any other Excel users you know. And while you're at it, please recommend that they sign up for my newsletter. Have a great day and keep on Excelling, Francis Hayes (The Excel Addict) Email: fhayes[AT]TheExcelAddict.com |
|
|
|
|
|
|
|
| Missed my last newsletter? | |
Click Here to View it Online |
|
| |
|
Having a positive attitude can help us in difficult times. I hope today's quote will help you foster a positive attitude today. Quote of the Day "While we are free to choose our actions, we are not free to choose the consequences of our actions." -- Stephen R. Covey -- |
|
|
If you have a favourite quote, send it to me
and I may post it in my newsletter.
|
|
THIS WEEK'S 'EXCEL IN MINUTES' TIP |
|
Find and Replace All In-Cell Line Breaks |
|
In Excel, it is not uncommon to see line breaks within cells. When you have text that you want to wrap to the next line at a specific point, you can simply press ALT+ENTER to insert a line break.  If you you want to get rid of the line break, you can click immediately before the line break and press Delete or click immediately after the line break and press Backspace. But what if you have a worksheet that contains a lot of cells with line breaks and you want to get rid of them all? Deleting them one by one isn't an appealing option. So, here's a simple solution to put in your Excel toolbox for whenever you need it. You can 'bulk remove' in-cell line breaks for a specific range of cells or an entire worksheet. To remove all line breaks from just a specific range of cells, select that range first. Otherwise, select any single cell. 1) Press CTRL+H to open the Find and Replace dialog. 2) Click in the 'Find what' field and press CTRL+J (i.e. the ASCII code for the line break character). It may appear that nothing was entered in the 'Find what' field but if you look closely, you'll see what appears to be a very tiny blinking dot near the bottom left corned. That blinking dot is actually the top of the blinking text cursor from the line below. If for some reason CTRL+J doesn't work for you, you can press Alt and type 0010 from the number keypad (i.e. not the numbers row at the top of your keyboard). 3) Next, click in the 'Replace with' field and press the spacebar once.  4) Finally, click the Replace All button. All of the in-cell line breaks have been replaced. Wrap Text Note that when a line break is inserted in a cell, Excel automatically applies Wrap Text formatting to the cell. So if you want to turn off the Wrap Text formatting from the cells where you removed the line breaks, select those cells and click Wrap Text on the Home tab in the Alignment group.  Replacing two blank spaces with one It may not be apparent but you may have caused some of the cells to now have two blank spaces if a blank space already existed before or after the line break.  You can easily find and get rid of the extra space by repeating the steps above, but using two blank spaces in the 'Find What' field and one blank space in the 'Replace with' field.  Use a formula to remove in-cell line breaks Alternatively you can use a formula to remove these line breaks. =SUBSTITUTE(A1,CHAR(10),"
")
 As in the previous example, if any of the cells originally included a blank space before or after the line break, they will now have two blank spaces. You can use the SUBSTITUTE function to replace two blank spaces with a single blank space by typing two spaces inside double quotes instead of CHR(10) in the formula. =SUBSTITUTE(A1,"
"," ")
 Removing in -cell line breaks with a macro If removing in-cell line breaks is a task you need to do often, consider using VBA (a macro) to speed things up. If you don't already have a Personal Macro Workbook to store your macros, go here to Create A Personal Macro Workbook. Once you have a PMW, you can record your own macros or find existing macro code and copy it into your PMW. Here's the code I use for removing in-cell line breaks. Sub
Remove_Line_Breaks_From_Selection()
With Selection 'Replace line breaks .Replace What:="" & Chr(10) & "", Replacement:=" ", LookAt:=xlPart 'Replace two blank spaces with one .Replace What:=" ", Replacement:=" ", LookAt:=xlPart 'Turn Wrap Text formatting .WrapText = False End With End Sub You can copy this into your PMW and start using it right away. Note that before running this macro, you will first need to select the cells you want to run the macro on. |
|
|
|
|
|
|
|
 |
|
|
|
|
 |
|
| Disclosure: Some of the resources I recommend on my website and in my newsletter pay me a small referral commission if you purchase from them through links on my website or using my referral code. This helps offset the costs of my website. I've worked long and hard to build up my reputation online over the past 16 years as someone who provides exceptional value to my readers. So I'm not willing to risk that. As you know, I don’t just recommend anything. It has to be of outstanding quality and value. If you are EVER not completely satisfied with anything I recommend, please let me know and you will get your money...GUARANTEED. You can't lose. | |