+ Reply to Thread
Results 1 to 14 of 14

copy cell text to part of other cell text

  1. #1
    Registered User
    Join Date
    03-29-2017
    Location
    Madrid
    MS-Off Ver
    2013
    Posts
    38

    copy cell text to part of other cell text

    Hi,

    I have thousands of rows with, lets say, 2 columns. The first column, A, has for each row a number of apples and the second column with the following text "Subject has X apples".
    I'd like to change the X text to the value given in column A
    Apples statement
    5 Subject has X apples
    So i'd like it to change automatically to:
    Apples statement
    5 Subject has 5 apples

    thanks!
    Last edited by gonurvia; 06-05-2017 at 08:16 AM.

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: copy cell text to part of other cell text

    in B2
    ="Subject has "&A2&" apples"

    Do you want a formula or a VBA solution?
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: copy cell text to part of other cell text

    Hi,

    ="Subject has " & A1 & " apples"

    However this is clearly a much simplified example and probably doesn't represent the size of your actual data. We often find that when we give an answer to what is a trivial non representative example when the solution is used with the real world data it doesn't work because of factors like string length and other stuff which is important but hasn't been mentioned.

    Therefore upload your real workbook (or at least a cut down copy), and manually add the results you expect to see. Clearly identify which is original data and which are the results and in a note clearly explain how you have arrived at your results.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Registered User
    Join Date
    03-29-2017
    Location
    Madrid
    MS-Off Ver
    2013
    Posts
    38

    Re: copy cell text to part of other cell text

    Hi, as you may see in the excel, there are more than 2 columns. The ones im interested in are B and H. Row 3 is an example of how I would like it to look like and row 2 is the basic text I have for the thousand of rows.
    Id like it to change column H text from
    PHP Code: 
    <img style="max-width:500px;" src="C:\Photos\X.jpg"
    to
    PHP Code: 
    <img style="max-width:500px;" src="C:\Photos\1639.jpg"
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    03-29-2017
    Location
    Madrid
    MS-Off Ver
    2013
    Posts
    38

    Re: copy cell text to part of other cell text

    And yeah Richard, that's exactly what happens with my data, I can't use special-k's solution as my text has several quotation marks

  6. #6
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: copy cell text to part of other cell text

    Hi,

    Perhaps this
    =SUBSTITUTE(H2,"X.jpg",B2&".jpg")
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  7. #7
    Registered User
    Join Date
    03-29-2017
    Location
    Madrid
    MS-Off Ver
    2013
    Posts
    38

    Re: copy cell text to part of other cell text

    won't let me use it as my cell has many quotation marks, <, > , &....

  8. #8
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: copy cell text to part of other cell text

    I'm afraid I don't understand. The formula I suggested will not have any problems with quotation marks in either cell.

  9. #9
    Registered User
    Join Date
    03-29-2017
    Location
    Madrid
    MS-Off Ver
    2013
    Posts
    38

    Re: copy cell text to part of other cell text

    It says there's an error with the formula. Do I need to write the formula on a specific cell or any given cell (ex. D10) is valid?

  10. #10
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: copy cell text to part of other cell text

    It may be that you require semicolons instead of commas based on your region
    =SUBSTITUTE(H2;"X.jpg";B2&".jpg")

  11. #11
    Registered User
    Join Date
    03-29-2017
    Location
    Madrid
    MS-Off Ver
    2013
    Posts
    38

    Re: copy cell text to part of other cell text

    ...it still won't work. Could you please try it on the attachement I posted earlier? I'm not sure if its not working because of my incompetence or just can't work in my excel

  12. #12
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: copy cell text to part of other cell text

    Certainly- here is the updated file.
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    03-29-2017
    Location
    Madrid
    MS-Off Ver
    2013
    Posts
    38

    Re: copy cell text to part of other cell text

    UGHHH.... thank you very much, I think the cause was that my excel is in spanish and didn't recognize "substitute" function.

  14. #14
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: copy cell text to part of other cell text

    Yes, that would explain it!

+ 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. Extract and Copy Part of Text String From one cell to another
    By Chica SM in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-22-2014, 10:39 AM
  2. Replies: 8
    Last Post: 01-28-2014, 05:15 PM
  3. Formula to identify part of a text in cell from range of cells & insert adjacent cell text
    By Novicebutnotforlong in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 11-19-2013, 02:11 AM
  4. Replies: 7
    Last Post: 01-24-2013, 06:55 PM
  5. [SOLVED] macro to copy part of text from a cell
    By Fugazi_Dent in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-19-2013, 07:07 PM
  6. Replies: 5
    Last Post: 04-08-2012, 01:06 AM
  7. [SOLVED] Use Macro to copy part of text in cell
    By PhilipsBernard in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-17-2005, 05: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