Excel - Replacing Carriage Returns With Spaces In Excel - Episode 1373
Microsoft Excel Tutorial: Cleaning Hard-Return and Replacing with a Space.
Welcome back to another episode of the MrExcel Podcast. In today's episode, we will be discussing the CLEAN function in Excel, and how to use it without losing important spaces in your data. Our friend Ram sent in a question about this, and we are here to provide the solution.
If you have ever used the CLEAN function, you know that it does a great job of removing unwanted characters, such as hard returns, from your data. However, in some cases, you may want to keep those spaces between words. This is where the SUBSTITUTE function comes in handy. Instead of using CLEAN, we can use SUBSTITUTE to replace the hard return with a space, giving us the desired result.
To use SUBSTITUTE, we need to specify the text we want to search for, which in this case is the data in cell A2. Then, we need to specify the old text we want to replace, which is the hard return. To type a hard return in a formula, we use the CHAR function and specify the ASCII code for a line feed, which is 10. Finally, we specify the new text we want to replace the old text with, which in this case is a space. The formula will look like this: =SUBSTITUTE(A2,CHAR(10)," ").
We hope this tip helps you in your Excel journey. Thank you to Ram for sending in the question, and thank you for tuning in to another netcast from MrExcel. Don't forget to subscribe to our channel for more Excel tips and tricks. See you next time!
Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/
You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-...
Table of Contents
(00:00) Excel CLEAN function
(00:17) Data with many Alt+Enter
(00:35) CLEAN does not replace Alt+Enter with a space
(00:45) Using SUBSTITUTE to change Alt+Enter to Space
(01:10) Clicking Like really helps the algorithm
#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial #excelformula #excelformulasandfunctions
This video answers these common search terms:
how to find and replace hard returns in excel
how to remove hard returns in excel
excel replace line breaks with space
excel formula to remove line breaks
remove hard returns and insert spaces in excel
replacing carriage returns with spaces in excel
space instead of line break in excel cell
excel formula to substitute line breaks with spaces
convert line breaks to spaces in excel
how to separate alt enter in excel
how do i hard return in an excel cell
how to do hard return in excel cell with formu;a
replacing new lines with spaces in excel
replacing paragraph breaks with spaces in excel
In Today's Podcast, Episode #1373, Ram is using =CLEAN to get rid of the hard-return in a cell, but when he does this, he is losing the space between the words in that cell. Bill shows us how to use the =SUBSTITUTE function instead to yield a better result.
Download a new two minute video every workday to learn one of the 377 tips from the book!
Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...
Смотрите видео Excel - Replacing Carriage Returns With Spaces In Excel - Episode 1373 онлайн без регистрации, длительностью часов минут секунд в хорошем качестве. Это видео добавил пользователь MrExcel.com 31 Март 2011, не забудьте поделиться им ссылкой с друзьями и знакомыми, на нашем сайте его посмотрели 757 раз и оно понравилось 10 людям.