+ Reply to Thread
Results 1 to 7 of 7

Replace cell in Column A with value in Column B if contains similar character string

  1. #1
    Registered User
    Join Date
    03-16-2015
    Location
    Las Vegas, Nevada
    MS-Off Ver
    2010
    Posts
    11

    Replace cell in Column A with value in Column B if contains similar character string

    Hello all! I am in need of a formula and am having a problem finding a solution.

    I have values in column A and in column B.
    Column A contains values with 4 to 8 characters, and Column A has 300 cells.
    IE: 1234, 1259T, G8797TB
    Column B contains a list of values that lead with the same values, but then has additional information, and the column has 100 cells.
    IE 1234_HELP_ME PLEASE, 1259T_SEEMS_SO EASY, G8797TB_AM_I DUMB

    I need to figure out a formula that will check if Column A has "1234" then replace with value in Column B "1234_HELP_ME PLEASE".

    I can resolve this by doing find and replace 100 times, but am sure there is a better way. Also the data in each Column are not sorted alphabetically if that matters. I am using Office 2010.

    Thank you so much for the help!

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,713

    Re: Replace cell in Column A with value in Column B if contains similar character string

    Hi, welcome to the forum

    1st, a formula cannot replace or change things in another cell, it can only affect the cell it is in.
    2nd, you can either have data in a cell or a formula, you cant have both.

    I suggest you add a helper column (C?) that will do the test you want, and then return the answer you need. However, your description is a bit confusing to me I suggest you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
    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
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2007
    Posts
    5,694

    Re: Replace cell in Column A with value in Column B if contains similar character string

    In C1:

    =MID(B1,LOOKUP(2,1/ISNUMBER(SEARCH($A$1:$A$3,B1)),LEN($A$1:$A$3))+1,255)

    Copy/pastvalue back to B1 if needed.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    03-16-2015
    Location
    Las Vegas, Nevada
    MS-Off Ver
    2010
    Posts
    11

    Re: Replace cell in Column A with value in Column B if contains similar character string

    Thanks! I have been learning about excel, and am excited to learn more on this forum!

    In the few formulas I have used, I have always used a helper column.

    Excel example of request.xlsx

    I believe I have attached the spreadsheet.
    Column A has the values that I want to keep. These values would be used to replace the values in Column B.
    Column B has the values that I want to change, with the corresponding values in Column A.
    Column C is what the final product would look like, after the formula or perhaps a find and replace macro.

    Let me know what you think, and thanks for the help!

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,713

    Re: Replace cell in Column A with value in Column B if contains similar character string

    Try this in your helper...
    =INDEX($A$2:$A$11,MATCH(B2&"*",$A$2:$A$11,0))

  6. #6
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2007
    Posts
    5,694

    Re: Replace cell in Column A with value in Column B if contains similar character string

    Updated:

    =LOOKUP(2,1/SEARCH(B2,$A$2:$A$11),$A$2:$A$11)

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,713

    Re: Replace cell in Column A with value in Column B if contains similar character string

    Happy to help, 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] replace nth occurrences of a character string within a cell...
    By herbie226 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-09-2014, 02:50 AM
  2. How to vlookup a Column contain special character and replace it?
    By miraclesuki in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-22-2013, 05:03 PM
  3. Change Cell Strings in Column to Replace Specifically the Second Character
    By Steve794421 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-21-2013, 09:36 AM
  4. Replies: 3
    Last Post: 08-29-2010, 03:31 PM
  5. [SOLVED] I want to replace the first character in a column of text
    By Biredndra in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-28-2005, 06:05 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