+ Reply to Thread
Results 1 to 12 of 12

VBA to convert cell to text and ensuring 7 digit number all along in entire column.

  1. 09-18-2013, 12:15 PM #1
    shiva_reshs is offline
    Forum Contributor
    Join Date
    07-17-2012
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    715

    VBA to convert cell to text and ensuring 7 digit number all along in entire column.

    Hi,

    I have series of number in column L, some of it are in Text format and some not.
    Can anyone help me with the VBA which converts entire row to text format along with ensuring all numbers are of & digit only?

    Thanks
    Reply With QuoteRegister To Reply
  2. 09-18-2013, 12:27 PM #2
    stnkynts is offline
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: VBA to convert cell to text and ensuring 7 digit number all along in entire column.

    Try this. It wasn't clear what you wanted to do about the cells that weren't 7 characters so I just put them in a message box.

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  3. 09-18-2013, 12:38 PM #3
    shiva_reshs is offline
    Forum Contributor
    Join Date
    07-17-2012
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    715

    Re: VBA to convert cell to text and ensuring 7 digit number all along in entire column.

    Hi,

    I am sorry, If I confused you. I have L column with numbers only. It will never have characters but just numbers.
    1) I want to ensure that all numbers are of 7 digit and if not then add 0 ( zeros ) in the beginning of number.
    2) Convert the cell format to text so would not loose the added 0(zeros)

    Please advice
    thanks
    Reply With QuoteRegister To Reply
  4. 09-18-2013, 01:28 PM #4
    stnkynts is offline
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: VBA to convert cell to text and ensuring 7 digit number all along in entire column.

    Try:

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  5. 09-18-2013, 02:16 PM #5
    shiva_reshs is offline
    Forum Contributor
    Join Date
    07-17-2012
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    715
    Here is the file.

    I tired your code, but I am gettin error message " Cell not found"
    Attached Files Attached Files
    Last edited by shiva_reshs; 09-18-2013 at 02:20 PM.
    Keep the Forum clean :


    1. Use [ code ] code tags [ /code ]. It keeps posts clean, easy-to-read, and maintains VBA formatting.
    2. Show appreciation to those who have helped you by clicking * Add Reputation below their posts.
    3. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.
    Reply With QuoteRegister To Reply
  6. 09-18-2013, 05:31 PM #6
    stnkynts is offline
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: VBA to convert cell to text and ensuring 7 digit number all along in entire column. 

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  7. 09-18-2013, 06:56 PM #7
    jindon is offline
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,835

    Re: VBA to convert cell to text and ensuring 7 digit number all along in entire column.

    Do you want it like tis?
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  8. 09-19-2013, 08:45 AM #8
    shiva_reshs is offline
    Forum Contributor
    Join Date
    07-17-2012
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    715

    Re: VBA to convert cell to text and ensuring 7 digit number all along in entire column.

    Hey stnkynts & jindon,

    Both of your code works perfectly.

    Jindon >> Where in your code it says that to look for column L? Though your code works, but just for my learning I wanted to know.

    Thanks
    Reps added.

    Regards,
    Reply With QuoteRegister To Reply
  9. 09-19-2013, 08:48 AM #9
    jindon is offline
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,835

    Re: VBA to convert cell to text and ensuring 7 digit number all along in entire column. 

    Please Login or Register  to view this content.
    ?
    Reply With QuoteRegister To Reply
  10. 09-19-2013, 09:28 AM #10
    shiva_reshs is offline
    Forum Contributor
    Join Date
    07-17-2012
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    715

    Re: VBA to convert cell to text and ensuring 7 digit number all along in entire column.

    so 16 means coloum L?

    So if I want to make it look for column B, then would it be column 2?

    Thanks
    Reply With QuoteRegister To Reply
  11. 09-19-2013, 09:31 AM #11
    jindon is offline
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,835

    Re: VBA to convert cell to text and ensuring 7 digit number all along in entire column.

    It's not 16, l6 , L6 in upper case.
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  12. 09-19-2013, 09:35 AM #12
    shiva_reshs is offline
    Forum Contributor
    Join Date
    07-17-2012
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    715

    Re: VBA to convert cell to text and ensuring 7 digit number all along in entire column.

    ohh!! I am sorry

    Thanks!!!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. convert to 2 digit number
    By pilotwings64 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-17-2019, 03:16 AM
  2. Delete the entire column that has single digit number - Part-2
    By plasma33 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-18-2012, 01:44 AM
  3. [SOLVED] Delete the entire rows that has single digit number
    By plasma33 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-09-2012, 08:17 AM
  4. [SOLVED] How do convert an entire column of text in Excel to all caps?
    By Carrie in forum Excel General
    Replies: 10
    Last Post: 03-17-2006, 07:10 PM
  5. 16 digit number wont keep alteration unless format cell to text
    By Croc001 in forum Excel General
    Replies: 3
    Last Post: 03-30-2005, 05:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1