Hi,
I have a spreadsheet that’s about 20,000 rows ( ! ) and I need to alter the contents of one column. In column D there are cells filled with info like this:
L42C2286
L1RY26
L4GH36
L4444PH888
And so on. What I need to do is put a space before the second letter (so after the L). So for example they would read:
L42 C2286
L4444 PH888
Can anyone help me with this one? I don't even know if I'm posting this in the right section as I'm an Excel novice...
[edited title, with apologies!]
Last edited by Monkey106; 08-30-2010 at 11:14 PM.
Hi Monkey, welcome to the forum.
While this isn't elegant, it should work for you:
This is an array formula and must be confirmed with CTRL+SHIFT+ENTER, not just ENTER. To avoid 20,000+ array formulas, you may want to use VBA instead. If you want to go that route let us know.
Please Login or Register to view this content.
Amazing - that works perfectly. Thank you so much for your help on this one, I would have never figured that out myself!
Hi Monkey,
Array Formulas are pretty daunting but so is VBA. Find attached a file that has a User Defined Function that will put a space in front of the second letter. It may be easier to use the VBA code than the Array Formula. Both are intermediate to advanced topics.
And if you want to enter Marvin's formula for 20,000 rows with VBA
Change column reference to suit.
Did not want to take away from your UDF Marvin but I thought it easier.Please Login or Register to view this content.
John
Thanks guys - this was just a quick job for a client and it's been sent off so all is well! Much appreciated.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks