silentdud Posted July 20, 2008 Share Posted July 20, 2008 I write VBA and VBS but it takes me a long time to write anything substantial and I can read much better than I can write it haha. Basically my problem is this: I need to convert certain entire columns with thousands of entries to text in VBA because there is a bug in excel which converts all but certain random cells to text, requiring it to be done manually. They are in number format and I want to ensure that every cell in that column is a number. It is something that I do over and over again in the same columns for about a hundred times, so it might save me a ton of time... Can someone write the few lines of code I would need to do this? I understand the concept of what I need to do and the steps I just cant get code it to work right and I know its a simple task for a good programmer. =) Thanks Michael Quote Link to comment Share on other sites More sharing options...
eman7613 Posted July 24, 2008 Share Posted July 24, 2008 there is a "record macro" button in excela and other m$ office products. simply do what you nee to do by hand once for one item, and then look at the code it generates for you (or use said generated macro to simply finish everything off). Quote Link to comment Share on other sites More sharing options...
krandor Posted July 24, 2008 Share Posted July 24, 2008 yep, a macro can do it. i found a macro on google once.. but i can't remember what my keywords were. try something like 'convery number to text macro excel'. maybe you'll get some hits like that. Quote Link to comment Share on other sites More sharing options...
PileOfMush Posted July 29, 2008 Share Posted July 29, 2008 Maybe you don't even need a macro... If you're faced with a problem like ZIP codes or SSNs with leading Zeros being DISPLAYED without the zeros (but the Zeros really are there), you can try creating another column with a formula like this... If your data is in col A, starting in A2, and your formula is in column B starting at B2... use the formula =A2&"" This will make 01234 display as 01234 instead of 1234 by forcing it to be interpreted as a string. You can then copy the column with the formulas and use Paste Special... Values to get rid of the formula. If the data in the column has actually been stripped of it's leading zero, and you need those zeros back... for a ZIP you can change the formula to this: =Right("00000"&A2,5) which will take 5 zeros, tack on your data, then take the right 5 chars. For a SSN, it's the same but use 9 zeros and ,9 instead of ,5.... yadda yadda. Also, if you're pasting this data into Excel, format the columns as Text before you paste. If none of this helps, print this advice out and burn it. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.