+ Reply to Thread
Results 1 to 19 of 19

Where is the paragraph text where you?re substituting the variables?

  1. #1
    Registered User
    Join Date
    03-15-2023
    Location
    usa
    MS-Off Ver
    1
    Posts
    9

    Where is the paragraph text where you?re substituting the variables?

    Answer the following questions about the formula below:

    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(AU2,"<competitor1>",$C2),"<competitor2>",$D2),"<usecase1>",$I2)


    Where is the paragraph text where you?re substituting the variables?
    Which cell can you find <competitor1>?
    Which cell can you find <competitor2>?
    Which cell can you find <usecase1>?


    ****

    And why is there three "substitute" in this formula?

    Thanks

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,087

    Re: Where is the paragraph text where you?re substituting the variables?

    Nothing is more frustrating than to be working on homework and to get stuck. Without guidance, you might have no hope of finishing the assignment before the deadline. The Internet can be a big help in that respect, but without learning how to get the right answer, asking others to solve your homework questions for you simply becomes a downward spiral (and it's cheating).

    We do not want to contribute to you cheating yourself out of your education, but we also acknowledge that seeking assistance to learn a concept is a legitimate request.

    If you are genuinely interested in receiving help in the form of tutoring or coaching, then please rephrase the title of your original post to clearly indicate you are seeking coaching or tutoring help. Any forum members (who are willing to assist as a tutor) will modify their responses accordingly to facilitate your learning. Tutors don't tell you the answers, they help you figure it out for yourself; so don't expect answers, expect suggestions, or just plain hints. Also, be specific in describing the function/formula or technique you trying to learn, and tell us what you have attempted so far. Otherwise, expect your plea for homework answers to be ignored.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: Where is the paragraph text where you?re substituting the variables?

    Is this homework?

    How will you learn if we just give you the answers?

    Pete

  4. #4
    Registered User
    Join Date
    03-15-2023
    Location
    usa
    MS-Off Ver
    1
    Posts
    9

    Re: Where is the paragraph text where you?re substituting the variables?

    No it's not homework, I'm in my 30's.

    I'm trying to learn Google sheets and I cannot make sense of the substitute formula or why there is three in this formula — and when I google it — people seem to use only one?

    Here are my answers:

    Where is the paragraph text where you’re substituting the variables? U2
    Which cell can you find <competitor1>? C2
    Which cell can you find <competitor2>? D2
    Which cell can you find <usecase1>? I12

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,087

    Re: Where is the paragraph text where you?re substituting the variables?

    And why is there three "substitute" in this formula?
    How many things are you replacing in the source cell/text? What are they? What does the original text look like? What does the output look like when you execute the formula? What things were replaced? What did it replace them with? Where did it find the source texts?

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,087

    Re: Where is the paragraph text where you?re substituting the variables?

    Sounded like homework. And you could be a mature student . And people do continue to study and take courses through their lives. It’s called "nesting" and you use it like this when you need to look for and replace more than one thing. Start from the "inside" one and work your way out.

    Your answers are correct, you just need to understand why. Might have been worth saying what you thought up front and there would not have been the confusion (on our part).

  7. #7
    Registered User
    Join Date
    03-15-2023
    Location
    usa
    MS-Off Ver
    1
    Posts
    9

    Re: Where is the paragraph text where you?re substituting the variables?

    No, I don''t understand.

  8. #8
    Registered User
    Join Date
    03-15-2023
    Location
    usa
    MS-Off Ver
    1
    Posts
    9

    Re: Where is the paragraph text where you?re substituting the variables?

    Okay so if they are correct, why is it U2 and not AU2 like it is in the formula?

    Thanks.

  9. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,087

    Re: Where is the paragraph text where you?re substituting the variables?

    Look at an individual SUBSTITUTE, starting with the middle one. Copy just that part of the formula and put it in its own cell with an equals sign at the beginning. Then apply the middle SUBSTITUTE to the output you get. Repeat with the third.

  10. #10
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,087

    Re: Where is the paragraph text where you?re substituting the variables?

    Presumably, cell U2 is where the formula is and it refers to the contents of cell AU2.

    So, I misunderstood. The text that is the source IS in cell AU2.

  11. #11
    Registered User
    Join Date
    03-15-2023
    Location
    usa
    MS-Off Ver
    1
    Posts
    9

    Re: Where is the paragraph text where you?re substituting the variables?

    WOW my brain just does not get this. Is it because this is a hard formula? Advanced? No? Lol

  12. #12
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,087

    Re: Where is the paragraph text where you?re substituting the variables?

    Make it easy for us to work with you …

    Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

  13. #13
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,087

    Re: Where is the paragraph text where you?re substituting the variables?

    No, not hard. Just new to you.

  14. #14
    Registered User
    Join Date
    03-15-2023
    Location
    usa
    MS-Off Ver
    1
    Posts
    9

    Re: Where is the paragraph text where you?re substituting the variables?

    Okay.

    Here's a file.

    Look at columns AB and AC. Based on them, how do I reverse engineer how the Substitution formulas work, and create the H3 formula for columns AD and AE?

    I assume H3 formula means "Header 3". A H3 is what we call a heading we use in Google Docs, it helps with SEO to rank articles published online containing have better chances of ranking in Google.
    Attached Images Attached Images
    Last edited by hockeyrules3434; 03-15-2023 at 07:20 PM.

  15. #15
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,087

    Re: Where is the paragraph text where you?re substituting the variables?

    That's not a file. It's a picture of a worksheet. Not even all of it.

  16. #16
    Registered User
    Join Date
    03-15-2023
    Location
    usa
    MS-Off Ver
    1
    Posts
    9

    Re: Where is the paragraph text where you?re substituting the variables?

    Is this right? =SUBSTITUTE(AD3,"<content>”,$J53)

  17. #17
    Registered User
    Join Date
    03-15-2023
    Location
    usa
    MS-Off Ver
    1
    Posts
    9

    Re: Where is the paragraph text where you?re substituting the variables?

    That's all I got. I don't think it's great myself.

  18. #18
    Registered User
    Join Date
    03-15-2023
    Location
    usa
    MS-Off Ver
    1
    Posts
    9

    Re: Where is the paragraph text where you?re substituting the variables?

    =SUBSTITUTE(AD3,"<alternative1>”,$J53)

    This?

  19. #19
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,087

    Re: Where is the paragraph text where you?re substituting the variables?

    See this simplified example

    As an example, contents of cell AU2 (look familiar?)
    Which cell can you find <competitor1>?
    Which cell can you find <competitor2>?
    Which cell can you find <usecase1>?

    =SUBSTITUTE(AU2,"<competitor1>",$C2)
    =SUBSTITUTE(SUBSTITUTE(AU2,"<competitor1>",$C2),"<competitor2>",$D2)
    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(AU2,"<competitor1>",$C2),"<competitor2>",$D2),"<usecase1>",$I2)

    =SUBSTITUTE(AU2,"<competitor1>",$C2)
    =SUBSTITUTE(U8,"<competitor2>",$D2)
    =SUBSTITUTE(U9,"<usecase1>",$I2)
    Attached Files Attached Files

+ 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] Substituting Text Strings
    By Jim Clayton in forum Excel General
    Replies: 13
    Last Post: 08-16-2018, 12:13 PM
  2. Substituting text but only if it is not part of another word
    By Sophie.Durrant in forum Excel General
    Replies: 2
    Last Post: 01-22-2015, 11:43 AM
  3. Substituting text
    By skatcb in forum Excel General
    Replies: 2
    Last Post: 06-15-2012, 08:04 AM
  4. Replies: 9
    Last Post: 02-20-2010, 04:43 PM
  5. Substituting text
    By TedH in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-15-2009, 03:31 PM
  6. [SOLVED] Replacing or Substituting Text
    By Naya in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-21-2005, 10:05 PM
  7. [SOLVED] substituting two text occurances in same cell.
    By Hassan Alameh in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-09-2005, 10:06 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