+ Reply to Thread
Results 1 to 16 of 16

Replace text string in a cell and retain rich text formatting (Excel 2013)

  1. #1
    Registered User
    Join Date
    04-15-2015
    Location
    Michigan
    MS-Off Ver
    2003 - 2013
    Posts
    7

    Replace text string in a cell and retain rich text formatting (Excel 2013)

    Hello:

    I need to replace the word "[[BREAK]]" inside of a cell with a line break (chr(10) or chr(13)) and keep the formatting on all of the other words in the cell.

    For example:

    Residence means:<<BREAK>>a. the structure blah, blah, blah...; or

    I need it to look like this:

    Residence means:
    a. the structure blah, blah, blah...; or

    Thanks in advance!

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,895

    Re: Replace text string in a cell and retain rich text formatting (Excel 2013)

    =SUBSTITUTE(A1,"<<BREAK>>",CHAR(10))

    But make sure that word wrap is on. Adjust cell width to suit...
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Replace text string in a cell and retain rich text formatting (Excel 2013)

    Welcome to the board.
    Please Login or Register  to view this content.
    Entia non sunt multiplicanda sine necessitate

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Replace text string in a cell and retain rich text formatting (Excel 2013)

    Shorter but slower:
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    04-15-2015
    Location
    Michigan
    MS-Off Ver
    2003 - 2013
    Posts
    7

    Re: Replace text string in a cell and retain rich text formatting (Excel 2013)

    Thank you, Glenn, but it is changing the formatting of the entire cell to bold.
    I need the text to retain the formatting.

  6. #6
    Registered User
    Join Date
    04-15-2015
    Location
    Michigan
    MS-Off Ver
    2003 - 2013
    Posts
    7

    Re: Replace text string in a cell and retain rich text formatting (Excel 2013)

    Thank you, shg, but I'm getting an error when I run the macro on this:

    Runtime error '1004':
    Insert method of Characters class failed

    Any thoughts??

  7. #7
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Replace text string in a cell and retain rich text formatting (Excel 2013)

    Maybe?

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    04-15-2015
    Location
    Michigan
    MS-Off Ver
    2003 - 2013
    Posts
    7

    Re: Replace text string in a cell and retain rich text formatting (Excel 2013)

    Thanks John, but it is also changing the formatting of the entire cell.
    I need to retain the formatting.

  9. #9
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Replace text string in a cell and retain rich text formatting (Excel 2013)

    Can you attach a sample file with some dummy data to test on?

  10. #10
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Replace text string in a cell and retain rich text formatting (Excel 2013)

    Try this...
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    04-15-2015
    Location
    Michigan
    MS-Off Ver
    2003 - 2013
    Posts
    7

    Re: Replace text string in a cell and retain rich text formatting (Excel 2013)

    Sample.xlsx

    This is just some dummy text, but it shows a sample of how it looks.
    There are actually over 20,000 cells that need to have text replaced.

    Thanks!

  12. #12
    Registered User
    Join Date
    04-15-2015
    Location
    Michigan
    MS-Off Ver
    2003 - 2013
    Posts
    7

    Re: Replace text string in a cell and retain rich text formatting (Excel 2013)

    Thank you, jindon, but I'm still getting an error when I run your code:

    Runtime error '1004':
    Insert method of Characters class failed

  13. #13
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Replace text string in a cell and retain rich text formatting (Excel 2013)

    Mine works fine on A2 in your example. It doesn't on the others because the text is longer than 255 characters, which is a limitation of the range.characters method. I have no other suggestion.

  14. #14
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Replace text string in a cell and retain rich text formatting (Excel 2013)

    Cahracter method has limit in relation to the length of characterts....
    change to
    Please Login or Register  to view this content.

  15. #15
    Registered User
    Join Date
    04-15-2015
    Location
    Michigan
    MS-Off Ver
    2003 - 2013
    Posts
    7

    Re: Replace text string in a cell and retain rich text formatting (Excel 2013)

    Thank you jindon and shg. That works for some of the cells. At least it's a start.

    I also have the option of pasting everything into the spreadsheet as plain (unbold) text.
    Is it possible to have a macro that can go through the spreadsheet and make certain words in each cell bold?
    I can compile a list of all of the words that need to be bold (i.e. "you, us, our, etc...").
    Would that work even with the character count limitations?

    Thanks!

  16. #16
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Replace text string in a cell and retain rich text formatting (Excel 2013)

    Something like this?
    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)

Similar Threads

  1. [SOLVED] Excel 2013: Copy Pivot Table Values to New Sheet and Retain Formatting
    By greatjobtoday in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 03-27-2015, 08:07 AM
  2. [SOLVED] Excel 2013 issue with text formatting
    By Sthlm in forum Excel General
    Replies: 7
    Last Post: 10-23-2013, 01:52 AM
  3. replace some text on a string in a cell
    By ELFM in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-09-2013, 12:24 AM
  4. [SOLVED] Cannot get a cell to retain formatting when a part of a text string
    By Grimace in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-17-2012, 12:08 AM
  5. Rich Text from Autoshape to Cell
    By John Michl in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-19-2006, 04:25 PM

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