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
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
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920