+ Reply to Thread
Results 1 to 5 of 5

attempting to modify a cell based on what text is within a different cell.

  1. #1
    Registered User
    Join Date
    02-19-2013
    Location
    MN, US
    MS-Off Ver
    Excel 2010
    Posts
    3

    attempting to modify a cell based on what text is within a different cell.

    So I have a spreadsheet where I need to output into a cell based on the contents of another cell.

    Here's some example code I have to kind of explain my situation.

    if ($a1 -contains "test" + X) {$g1 = ProgramID + "-t" + X} ELSE {$g1 = "ProgramID}

    name, ProgramID, and X are both variables
    name could be any name of program
    X could be any number between 1 and 99, so I can't use nested if statements to match the numbers.
    ProgramID is just a number that represents the program amd concatenating to this should be fine.

    the problem I have is that $a1 could have text after testX, or testX could contain multiple double digits which means you can't use Right to get values.

    Here are some examples of $a1 value formats

    name-test12
    name-test 49
    name-test 1-final
    name-test 22-f
    name-test 8- final
    name-final-test 13
    name
    name-final

    If $a1 doesn't contain testx $g1 should just be ProgramID. In other words, final can always be ignored.

    I'm really not knowledgeable enough with excel to figure this one out.

    Thanks for any help, even if you have some direction I could explore to make this work.

  2. #2
    Valued Forum Contributor spitfireblue's Avatar
    Join Date
    01-29-2015
    Location
    Adelaide, Australia
    MS-Off Ver
    2007,2010,2016
    Posts
    611

    Re: attempting to modify a cell based on what text is within a different cell.

    Where are you getting ProgramID from?
    I think this is what you are after...

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Regards,
    Stephen

    If you feel someone has helped you please thank them and click on the star on their post to add reputation.
    Please ensure that you use code tags where relevant, and mark solved threads as solved.
    Most of all please be respectful and understanding of others.

  3. #3
    Registered User
    Join Date
    02-19-2013
    Location
    MN, US
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: attempting to modify a cell based on what text is within a different cell.

    ProgramID comes from I1.

    This was pretty close, unfortunately this formula is grabbing numbers from A1. A1 sometimes has numbers in the name as well, I didn't think of that.

    Here's an example of how it outputs.

    Name25-test12 = programid-t25

    Is there a way to do something like a regex to search for the word "test" and find up to two numbers after it?

  4. #4
    Valued Forum Contributor spitfireblue's Avatar
    Join Date
    01-29-2015
    Location
    Adelaide, Australia
    MS-Off Ver
    2007,2010,2016
    Posts
    611

    Re: attempting to modify a cell based on what text is within a different cell.

    Yep, the find function has an option to start at a certain point within the text. Try this...

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    02-19-2013
    Location
    MN, US
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: attempting to modify a cell based on what text is within a different cell.

    This looks like it's working fantastically! Awesome! Thanks a ton!

+ 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. Excel 2003 formula - modify one cell, another cell track the modify date
    By baby0596cw in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 11-06-2014, 12:55 PM
  2. Attempting to Highlight Cells Based on String within Cell
    By rmdlp in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-23-2014, 04:30 PM
  3. Modify Cell Background Color based on cell content
    By CWDurkin in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-01-2014, 11:57 AM
  4. Modify formulas in range based on cell value
    By podunkus in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 11-17-2013, 09:40 PM
  5. [SOLVED] Attempting to define content in cell based on update of other cell in same row.
    By ychartra01 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-04-2013, 07:04 PM
  6. VBA modify value based on conditions of another cell
    By sk8shorty01 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-26-2011, 01:20 PM
  7. Modify formula to not execute if cell contains certain text
    By icets in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-26-2011, 08:59 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