Excel - Left 26, Even If Only 10: Episode 1463

829 views · Published 3 November 2011 · 2:02 · Indexed 8 November 2026

Channel: MrExcel.com · 2011 · Education

Watch on YouTube

Microsoft Excel Tutorial: How to Keep the Left 26 Characters of a Cell in Excel | MrExcel Podcast.

Welcome to another episode of the MrExcel podcast. In today's episode, we will be discussing how to keep the left 26 characters of a cell, even if it's only 10 characters long. This question was sent in by Shrikant, who is trying to create journal entries in Excel. He needs to have 26 characters of description followed by the account, starting at character position 27. However, when the description is less than 26 characters, he needs to add spaces to make it work. Let's see how we can solve this problem.

To start off, we can use the LEFT function to cut off the first 26 characters of the cell. This is easy enough, but it doesn't solve the problem for shorter descriptions. In order to add the necessary spaces, we can use the REPT function. This function allows us to repeat a specific character or text a certain number of times. In this case, we will use it to add spaces to the end of the description.

So, our formula will be =LEFT(A2,26)&REPT(" ",26). This will add 26 spaces to the end of the description, ensuring that the account starts at character position 27. This method works for both longer and shorter descriptions, making it a versatile solution. Of course, you can adjust the number of spaces depending on your specific needs.

In this video, we have used a fixed width font to show that everything lines up perfectly. However, in reality, you would be using a standard font like Arial or Calibri. So, don't worry if it doesn't look perfectly aligned in your Excel sheet. As long as the account starts at character position 27, you're good to go. Thank you, Shrikant, for sending in this question and thank you for watching. Be sure to tune in next time for more helpful tips and tricks from MrExcel.

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-on-youtube/


Table of Contents:
(00:00) Keep the Left 26 
(00:15) Software requirements for journal entries 
(00:27) Solution using LEFT function 
(00:40) Adding spaces for shorter entries 
(01:02) Using REPT function 
(01:15) Explanation of solution 
(01:26) Using fixed width font for perfect alignment 
(01:42) 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:
Add spaces
Character position 27
Cut off at 26
Episode 1463
Fixed width font
Journal entries
Keep the Left 26
Learn Excel
REPT function
Software upload


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153141/


Shrikant needs to join the left 26 characters of column A with column B. But...even if A2 only has 10 characters, he needs it to fill with spaces so that the value from B starts in character position 27 each and every time.
In Episode #1463, Bill shows us how to maintain this positioning of data.

...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!

"The Learn Excel from MrExcel Podcast Series"

MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!

More from this channel