+ Reply to Thread
Results 1 to 12 of 12

In a specific column find text and change the format

  1. #1
    Registered User
    Join Date
    01-22-2014
    Location
    Pretoria, South Africa
    MS-Off Ver
    Excel 2010
    Posts
    58

    In a specific column find text and change the format

    Good morning forum members,

    Along the same lines as my previous post....

    a) In column C, I need to find all text that is:

    Capital letters, not bold, not underlined

    and change it to: Proper text, bold underlined


    and then also find text that is:


    Proper text, not bold, not underlined

    and change it to: bold, underlined


    b) In column D, there is "invisible content" in the cells next to the text that I want to reformat, which prevents the text from displaying fully. I need to find all of these and remove the "invisible content" so that the text in column C displays properly.

    I am attaching an example again.


    In anticipation,

    Louisa
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: In a specific column find text and change the format

    See attached.

    I put 999999 as an "end-of-file" delimiter in column A.

    I don't I understand the "invisible content" as all text appears to display fully.


    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by JohnTopley; 10-15-2015 at 04:13 AM.

  3. #3
    Registered User
    Join Date
    01-22-2014
    Location
    Pretoria, South Africa
    MS-Off Ver
    Excel 2010
    Posts
    58

    Re: In a specific column find text and change the format

    Hallo John, thank you the proper case and underline works. I do however not see the "bold" part. Is it possible to include this in the code also?

    Regarding the display, the client template requires that the column width be fixed as in my original example. Expanding the column display the names properly, but with the fixed width as per the example, the names do not display properly across the columns. If I click on the cell next to the name and press delete, something ("invisible content" is removed from the cell and the names then displays across the columns.

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: In a specific column find text and change the format

    Amended code: "delete" of cells in D appears to give you the result

    Please Login or Register  to view this content.
    Last edited by JohnTopley; 10-15-2015 at 07:57 AM. Reason: Updated macro

  5. #5
    Registered User
    Join Date
    01-22-2014
    Location
    Pretoria, South Africa
    MS-Off Ver
    Excel 2010
    Posts
    58

    Re: In a specific column find text and change the format

    Good morning John, i tried to run the revised code, but receives a runtime error. See attached screenshots

    Runtime error.png1004 error.jpg

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: In a specific column find text and change the format

    This is because there is no 999999 in column A (at end of data) to signify end of file. Despite this, the file will be converted.

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: In a specific column find text and change the format

    Code to remove need for 999999 in column A


    Please Login or Register  to view this content.
    Last edited by JohnTopley; 10-16-2015 at 06:16 AM. Reason: Correct error

  8. #8
    Registered User
    Join Date
    01-22-2014
    Location
    Pretoria, South Africa
    MS-Off Ver
    Excel 2010
    Posts
    58

    Re: In a specific column find text and change the format

    Thank you John, this worked like a charm again. Much appreciated.

    Regards,
    Louisa

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: In a specific column find text and change the format

    Thank you for the feedback; Minor change to macro (see previous posting). I need to be awake before I log on to the forum!!!

  10. #10
    Registered User
    Join Date
    01-22-2014
    Location
    Pretoria, South Africa
    MS-Off Ver
    Excel 2010
    Posts
    58

    Re: In a specific column find text and change the format

    Hallo John, apologies to come back to this one again. The client changed their minds and no longer wants the months as part of the structure.

    I removed the months and now I have to start from scratch sort of.....I tried to adapt the code myself but, being a bit clueless in this regard and not understanding properly what the elements of the code means, did not get very far.

    I created a new workbook showing in the first 10 rows what the new structure should look like, with rows 11-60 showing what the text looks like with the months and the formatting removed.

    Can you please assist with adapting the code to:

    a) Only find the two notes in grey italics, change the formatting and merge across the columns as indicated
    b) Adjust the row height to display the first note in full
    c) remove the "invisible content" in column D so that the full text displays across the columns?
    Attached Files Attached Files

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: In a specific column find text and change the format

    I am not going to be able to look at this for a few days so it might be worth re-posting as it is marked as SOLVED.

  12. #12
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: In a specific column find text and change the format

    See attached: hope this is what you want.

    Note you may need to change the statement for "lastr" to reflect the range with the last row

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by JohnTopley; 10-23-2015 at 02:39 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Finding specific text and change the format
    By Louisa Venter in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-20-2015, 05:11 PM
  2. [SOLVED] VBA to find text in specific format in a workbook
    By chataylo in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-14-2014, 06:37 PM
  3. [SOLVED] how do I format a column to change color when another column has a specific word in it?
    By officegirl14 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-23-2014, 06:15 PM
  4. [SOLVED] Search through columns and find specific text and replace that text with header column
    By adamzee in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-09-2014, 11:59 PM
  5. vba Find specific text font and change color
    By jacgeld in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-18-2014, 08:16 AM
  6. [SOLVED] Find Specific Text in Text String and Return Value in Adjacent Column
    By watchouse in forum Excel General
    Replies: 2
    Last Post: 07-11-2012, 03:53 PM
  7. fastest way to find the row where a specific text is found in a specific column
    By getgray in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-23-2011, 02:45 PM

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