+ Reply to Thread
Results 1 to 9 of 9

Macro to change cell format to text and remove unwanted spaces & special characters

  1. #1
    Registered User
    Join Date
    07-17-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2010
    Posts
    21

    Macro to change cell format to text and remove unwanted spaces & special characters

    Hi,

    I am looking for a macro where I can change the format of multiple columns after removing special character or spaces before and after the text. I am attaching a sample excel workbook where I have explained how the format I am looking for. Please help....thanks in advance.

    Regards,
    Chito
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    11-15-2008
    Location
    ph
    MS-Off Ver
    2007/2010/2016
    Posts
    479

    Re: Macro to change cell format to text and remove unwanted spaces & special characters

    Hi -

    try
    Please Login or Register  to view this content.
    Regards,
    Event

  3. #3
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    18,510

    Re: Macro to change cell format to text and remove unwanted spaces & special characters

    This should do.
    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    07-17-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Macro to change cell format to text and remove unwanted spaces & special characters

    Hi Even and Jindon, thanks a lot for the response. I am very close to get my desired solution from both of you.

    I like everything in the macro provided by Even but only my concern is all the blank cells seems to get some value, it showing in the count (please see attached screenshot "even21.jpg").
    Attachment 200674

    In that case Jindon's macro would have done everything but I want to see green indicator shown in the corner of cell like in F column (please see attached screenshot "jindon.jpg").
    Attachment 200675

    My actual scenario is,
    • Format of the columns are fixed (Text A to F, H, J to Q, Z, AC, AD, AE, AJ, AL, AP & AR. Currency in $ - AF & AQ. Date (d/m/yyyy) - AH & AI)
    • Rows are not fixed, it may have 200 one day or may have 6000 in another day in my data. So, it will be good if this macro checks only the cells which has some value in it

    I am going to do some modification from the below macros as trial but if you guys can help to get the macro what I am looking for, that would be great.

  5. #5
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    18,510

    Re: Macro to change cell format to text and remove unwanted spaces & special characters

    Why you need to see error sign?
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    07-17-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Macro to change cell format to text and remove unwanted spaces & special characters

    Hi Jidon,

    I tried to modify your macro but not quite getting it, I am very new to macro, hope you will bare with my questions.

    Here I am attaching the actual scenario, all colums are "text" format except AF & AQ in "currency" format and AH & AI in "date" format. Please do not include heading (1st row) in formatting, and if you can skip all the blanks cell that would be great.

    Cell format.xls

  7. #7
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    18,510

    Re: Macro to change cell format to text and remove unwanted spaces & special characters

    Test this
    Please Login or Register  to view this content.
    Last edited by jindon; 12-17-2012 at 04:15 AM.

  8. #8
    Registered User
    Join Date
    07-17-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Macro to change cell format to text and remove unwanted spaces & special characters

    Hi Jindon,

    I have changed this section and works perfectly, thanks alot

    Please Login or Register  to view this content.

  9. #9
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    18,510

    Re: Macro to change cell format to text and remove unwanted spaces & special characters

    Ahhh, typo
    Shold be
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1