+ Reply to Thread
Results 1 to 14 of 14

Replace text in cell with text from comment

  1. #1
    Registered User
    Join Date
    05-30-2018
    Location
    greece
    MS-Off Ver
    365
    Posts
    6

    Replace text in cell with text from comment

    Hi to all.
    I was wondering if there is a vba to search inside entire sheet and to do the following:
    In cells that there are comments, then replace the text inside these cells with the text of the comments of these cells.
    The cells that don't have comments to be leaved as they are.

    So i am looking for a batch process to do the above but in sheets i needed not in all workbook.
    F.e if a file has sheets 2020, 2021, 2022, 2023 to be able to choose in which sheets of these will run the vba.

    Thanks in advance for your time,
    Yiannis St

    Cross-post:
    Last edited by kalagas; 02-04-2020 at 11:29 AM.

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Replace text in cell with text from comment

    This should get you started https://contextures.com/xlcomments03.html#CopyAdjacent

  3. #3
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Replace text in cell with text from comment

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

    I will add it for you this time.Please read forum rules before continuing. Thx

    https://www.mrexcel.com/board/find-threads/unanswered

  4. #4
    Registered User
    Join Date
    05-30-2018
    Location
    greece
    MS-Off Ver
    365
    Posts
    6

    Re: Replace text in cell with text from comment

    Quote Originally Posted by Pepe Le Mokko View Post
    This should get you started
    The vba you sent me right:
    "The following macro will copy comment text to the cell to the right, if that cell is empty."

    Sorry but i need to replace the text inside the cell that the comment is.
    And i don't have the knowledge to make changes in the vba you sent me.

  5. #5
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Replace text in cell with text from comment

    As I said, "this will get you started"..... try to find the rest on the forum perhaps...

  6. #6
    Registered User
    Join Date
    05-30-2018
    Location
    greece
    MS-Off Ver
    365
    Posts
    6

    Re: Replace text in cell with text from comment

    Quote Originally Posted by Pepe Le Mokko View Post
    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see about cross-posting and adjust accordingly. Read to understand why we (and other sites like us) consider this to be important.

    I will add it for you this time.Please read forum rules before continuing. Thx
    I can't post links as i am new member, so i can not paste the cross-post link here.

  7. #7
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Replace text in cell with text from comment

    Please read the ENTIRE post, especially the last sentence.....

  8. #8
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    Office 365 Windows 10
    Posts
    2,286

    Re: Replace text in cell with text from comment

    Please Login or Register  to view this content.
    I cant verify as excel 2016 has changed comment type by the looks of it
    Last edited by nigelog; 02-04-2020 at 11:57 AM.

  9. #9
    Registered User
    Join Date
    05-30-2018
    Location
    greece
    MS-Off Ver
    365
    Posts
    6

    Re: Replace text in cell with text from comment

    Unfortunately it doesn't do anything when i run it

  10. #10
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    Office 365 Windows 10
    Posts
    2,286

    Re: Replace text in cell with text from comment

    As I said I cant verify the code in that link

  11. #11
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    Office 365 Windows 10
    Posts
    2,286

    Re: Replace text in cell with text from comment

    Comments have been changed to "notes" in 2016 rather than comments which start a conversation. Code works perfectly

  12. #12
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Replace text in cell with text from comment

    Here is the correct link to your crosspost.
    https://www.mrexcel.com/board/thread...mment.1122923/

    nigelog's code works assuming
    1. the active sheet has comments (these are the old style ones now called Notes)
    2. the cell with a comment does not already contain text or formula
    Cheers
    Andy
    www.andypope.info

  13. #13
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    Office 365 Windows 10
    Posts
    2,286

    Re: Replace text in cell with text from comment

    Apologies, you have text already in cells - remove red part

    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    05-30-2018
    Location
    greece
    MS-Off Ver
    365
    Posts
    6

    Re: Replace text in cell with text from comment

    ok this one worked (removed red lines).
    Thanks very much for your time.

+ 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] Temporarliy replace text with 3 dots when cell text overflows
    By jojo101 in forum Excel General
    Replies: 6
    Last Post: 02-19-2022, 05:34 PM
  2. Replies: 2
    Last Post: 01-20-2018, 09:59 AM
  3. Replies: 1
    Last Post: 07-20-2016, 10:12 AM
  4. [SOLVED] Show cell value(text) in comment box text with a particular arrangement
    By bkwins in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 07-25-2015, 02:15 PM
  5. Replace text string in a cell and retain rich text formatting (Excel 2013)
    By CharlieBear in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 04-15-2015, 04:39 PM
  6. Show cell value(text) in comment box text, or mouse tool tip on an gif icon
    By JIBG in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 05-19-2014, 06:50 AM
  7. Cell's text / comment (no comment )
    By Marianus in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-22-2013, 06:05 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1