Excel - Excel Tutorial: How to Split Words into 20 Characters in Excel (No Wrap Text) - Episode 1102
Microsoft Excel Tutorial: How to Split Words into 20 Characters in Excel (No Wrap Text).
Welcome back to the MrExcel netcast, where we help you master Excel one step at a time. I'm Bill Jelen, and today's question comes from Dack, who is facing a challenging problem. He needs to break words in a cell into exactly 20 characters and have them extend down from the original area. This may seem like a daunting task, but fear not, we have a solution for you.
At first, I tried using the LAN function to make sure all the words were 20 characters wide, and then used the CONCATENATE function to put them all together. However, this did not work due to the proportional spaced fonts in Excel. But then, I remembered an old-school trick that did the job perfectly. By copying the words and pasting them as values, and changing the font to a fixed spaced one like Courier or Courier New, I was able to use the Edit Fill Justify function to achieve the desired result.
However, this method required some tweaking, as the column width had to be set to 22.1 instead of the expected 20. Also, the last character in the cell had to be a space, which would get truncated, resulting in 19 characters instead of 20. So, I turned to a formula that I learned from Mike Gervin at "Excel is fun". This formula uses the MID function and an array formula to split the words into 20 character chunks, without the need for any manual adjustments.
To use this formula, simply select the range of cells where you want the words to be split, and enter the formula that points to the first cell in the range. Then, use the ROW function to generate the numbers 1, 21, 41, etc. and multiply it by 20 to get the desired character count. Finally, press control+shift+enter to enter the formula as an array, and voila, your words will be split into 20 character chunks, regardless of the font or spacing.
Of course, if this is something you have to do frequently, it might be easier to write a macro that automates the process. But for a one-time task, this array formula is the way to go. Thank you, Dack, for sending in your 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) Introduction
(00:16) The problem: breaking words into 20 characters
(00:36) Using Edit Fill Justify
(00:51) Trying a different approach
(01:41) Using a formula from "Excel is fun"
(02:17) Using the MID function
(03:00) 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
This video answers these common search terms:
Alternative methods for breaking text into 20 character chunks in Excel.
Fixing text formatting issues with proportional spaced fonts in Excel.
How to break words into 20 characters in Excel.
Setting column width in Excel for text formatting.
Tips for efficient data analysis in Excel.
Using array formulas in Excel for advanced text manipulation.
Using fixed spaced fonts (Courier or Courier New) in Excel.
Using the CONCATENATE function in Excel.
Using the LAN function in Excel.
Using the MID function in Excel to split text into 20 character chunks.
Word wrapping in Excel using Edit Fill Justify.
YouTube video on analyzing data with pivot tables.
Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...
Dak asks how to take a long cell entry and to split it into 20 character entries going down a column without using wrap text. Episode 1102 shows a couple of ways to solve the problem.
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of 377 tips from the book!
Смотрите видео Excel - Excel Tutorial: How to Split Words into 20 Characters in Excel (No Wrap Text) - Episode 1102 онлайн без регистрации, длительностью часов минут секунд в хорошем качестве. Это видео добавил пользователь MrExcel.com 17 Сентябрь 2009, не забудьте поделиться им ссылкой с друзьями и знакомыми, на нашем сайте его посмотрели 3,271 раз и оно понравилось 3 людям.