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!
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!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast episode, 1463.
Keep the Left 26 (Even if it's only 10) All right! Today's question sent in by Shrikant.
Shrikant is trying to create journal entries over here and he has a description and an account and they decided that the way to upload this to their software is they need 26 characters of description and then the account.
But the account has to start in character position 27.
That's what the software is expecting out there.
So, he needs to cut this off at 26, that's easy that's equal left of A2 comma 26.
But when it's less than 26, he needs to add spaces in order to get that to work.
So, right now, he's trying to equal A2, ampersand, B2 of course you know, we need the left.
Something comma 26 in order to pull that off, but that's not going to solve the problem.
It'll solve run for the longer ones, that'll be great but for the shorter ones see we need to add some spaces there.
So, my method is equal A2 ampersand REPT.
Repeat, a [ space ] comma either well, let's just put in 26 you know, it has to be something big enough to know that we will always end up with enough spaces, [ ctrl enter ].
Turn and see that gets it all to line up.
What is repeat doing and saying, hey give me 26 spaces and add it on to the end of A2.
That way here, this is maybe 10 characters, is now 36 characters.
And then the left takes it back to 26, to make sure that everything lines up, of course.
I'm using a quarry or new font here to show that it's lining up in reality you would be using Aerial in the old Excel or Calibri.
Well, look like it is lineup but it really if you use a fixed width font, gives line up perfectly.
So, again that's equal left A2 ampersand repeat REPT.
Open parenthesis, space comma 26 and then comma 26 that's for the left.
Thanks Shrikant for sending that question in and thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast episode, 1463.
Keep the Left 26 (Even if it's only 10) All right! Today's question sent in by Shrikant.
Shrikant is trying to create journal entries over here and he has a description and an account and they decided that the way to upload this to their software is they need 26 characters of description and then the account.
But the account has to start in character position 27.
That's what the software is expecting out there.
So, he needs to cut this off at 26, that's easy that's equal left of A2 comma 26.
But when it's less than 26, he needs to add spaces in order to get that to work.
So, right now, he's trying to equal A2, ampersand, B2 of course you know, we need the left.
Something comma 26 in order to pull that off, but that's not going to solve the problem.
It'll solve run for the longer ones, that'll be great but for the shorter ones see we need to add some spaces there.
So, my method is equal A2 ampersand REPT.
Repeat, a [ space ] comma either well, let's just put in 26 you know, it has to be something big enough to know that we will always end up with enough spaces, [ ctrl enter ].
Turn and see that gets it all to line up.
What is repeat doing and saying, hey give me 26 spaces and add it on to the end of A2.
That way here, this is maybe 10 characters, is now 36 characters.
And then the left takes it back to 26, to make sure that everything lines up, of course.
I'm using a quarry or new font here to show that it's lining up in reality you would be using Aerial in the old Excel or Calibri.
Well, look like it is lineup but it really if you use a fixed width font, gives line up perfectly.
So, again that's equal left A2 ampersand repeat REPT.
Open parenthesis, space comma 26 and then comma 26 that's for the left.
Thanks Shrikant for sending that question in and thank you for stopping by, we'll see you next time for another netcast from MrExcel.
