+ Reply to Thread
Results 1 to 18 of 18

Two Formulas Into One?

  1. #1
    Registered User
    Join Date
    12-16-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    16

    Question Two Formulas Into One?

    I ran into another situation where I could really use your help! First, I have text in G2 that has {J} and {K} that needs to be substituted for. The substitution text for {J} is in position J2 and the substitution text for {K} is in position K2. Do you know of any formulas that would substitute the botttom two formulas (assuming they are correct) into one formula?

    =SUBSTITUTE(G2,"{J}",J2)

    =SUBSTITUTE(G2,"{K}",K2)

    P.S. Do you think something like this would work?

    Thanks,

    DukeURL
    Last edited by DukeURL; 12-17-2013 at 12:03 AM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Two Formulas Into One?

    That should work. Try his combination...
    =SUBSTITUTE(SUBSTITUTE(G2,"{K}",K2),"{J}",J2)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    12-16-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Two Formulas Into One?

    Thank You! -- what's the best way to do both? For example, if the text is in column G2, should I put your formula in H2? If not, what do you suggest...

    Thanks,

    DukeURL

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Two Formulas Into One?

    Put that wherever you want the answer to be (H2?). It is testing G2 for both of those criteria (in 1 go), and replacing with the contents of K2 and J2, where applicable

  5. #5
    Registered User
    Join Date
    12-16-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Two Formulas Into One?

    Oh, thanks for helping me to understand! Well, I pasted your formula into the H2 cell and clicked on enter and got a blank cell. Also, G2 contains about 400 words and I was hoping that you knew of a way to be able to populate the cells below with the same text. Usually I would just drag the little "box" but I cannot see it the way the text is. Any ideas on either of these two problems?

    Thanks,

    DukeURL

    edit: On second thought, maybe it's working but I can't see it because it may ba at the bottom of the cell where I cannot see...ideas?
    Last edited by DukeURL; 12-17-2013 at 12:27 AM.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Two Formulas Into One?

    Im not really understanding where you are going on this (my fault, not yours). Could you upload a (clean) sample workbook showing what you have and what you want?
    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.

  7. #7
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Two Formulas Into One?

    In addition refer the G2 with UPPER(G2) to avoid case sensitivity conflicts


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  8. #8
    Registered User
    Join Date
    12-16-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Two Formulas Into One?

    Hello sixthsense, like this?: =SUBSTITUTE(SUBSTITUTE(UPPER(G2),"{K}",K2),"{J}",J2)

    Also, not to be difficult, I cannot upload my text in the document because of sensitivity issues. However, to clarify, the text, approx. 400 words, is too big for cell -- meaning it's to big for my computer screen and I cannot see it all. I wanted to be able to click on the bottom right of the cell so I could drag it down to all cells but again, the cell is "too big." Any ideas on how to fix this?

    Thanks,

    DukeURL

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Two Formulas Into One?

    Have you tried to increase the row height?

  10. #10
    Registered User
    Join Date
    12-16-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Two Formulas Into One?

    No, I don't know how to do that...searching google now...thanks for the terminology!

  11. #11
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Two Formulas Into One?

    Have you converted the arrived formula result to values and viewing it?

  12. #12
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Two Formulas Into One?

    hover the mouse over the row "divider", you will see it changes to an up-down arrow. click and hold, and drag the divider down to suite.
    You may need to format the cell as "wrap text" - in fact, just doing that may set the row height for you (probably by too much though)

    Another way is to right-click on a row number, click row height and enter a number - that takes a bit of playing around though

  13. #13
    Registered User
    Join Date
    12-16-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Two Formulas Into One?

    yep, that did it,, thanks! I automatically adjusted the row height and everything is perfect now. Thank you all for your help! Wow, I sure learned a lot today!!!

  14. #14
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Two Formulas Into One?

    you'r welcome. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below my 1st post to you)

  15. #15
    Registered User
    Join Date
    12-16-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    16

    {SOLVED} Re: Two Formulas Into One?

    solved......

  16. #16
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: {SOLVED} Re: Two Formulas Into One?

    Select Thread Tools from the menu link above and mark this thread as SOLVED.

  17. #17
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Two Formulas Into One?

    LOL ok, so I guess sometimes the forum puts my signature on each of my posts - and sometimes not

    You use THREAD TOOLS near the top if the page to mark threads SOLVED. I will do it for you this time

    oooo....k so this time it appears

  18. #18
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Two Formulas Into One?

    Happy to help and thanks for the feedback

+ 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] Need Formulas to Hide Partial Concatenate Data and Help Determining Two Other Formulas
    By Mattdim805 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 12-13-2013, 04:11 PM
  2. Using Cell references in file paths for formulas to create dynamic formulas
    By MichaelStokesJr in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-04-2013, 11:49 AM
  3. Replies: 5
    Last Post: 09-25-2013, 02:51 PM
  4. Ctrl+Shift+Enter (CSE) array formulas in VBA using differing formulas
    By officeguy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-11-2012, 04:31 PM
  5. Replies: 5
    Last Post: 05-05-2008, 02:22 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