Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Formula needed

0

I have column A (first name) and column B (last name). Both are filled up with names (around fifty names). I have to look for a first name (Marry) in column A and wherever I find it, I have to modify the existing (ex: Stone) corresponding last name from column B with a different one (Kay) so in the end,  instead of Marry Stone (like in the beginning) it will show Marry Kay. Help me please with a formula!

Answer
Discuss

Discussion

You need to show an example. Need better description of the Logic. How did you find Kay?
queue (rep: 467) Oct 27, '17 at 3:09 pm
the entire name is Anne-Marry Stone, but because Stone it's a mistake, I have to change it with Kay. So wherever I find Marry in column A, in the column B whatever is the last name, it has to change to Kay. In the end it has to be Anne-Marry (in column A) and Kay (in column B). 
dex Oct 28, '17 at 12:18 am
Add to Discussion

Answers

0

By your description this is a task for VBA because a worksheet function can't change any cell's value. However, the nearest to a solution would be the use of the VLOOKUP function. VLOOKUP has the following instruction.

"Find the LookUp-Value in the first column of the LookUp-Range using the LookUp-Match rule and return the value in the Return-Column of the row where you found it." For example, =VLOOKUP([LookUp-Value], [LookUp-Range], [Return-Column], [LookUp-Match]) which might look like this,

=VLOOKUP(A2, A3:A50, 2, FALSE)

Each of the 4 arguments has its own rules which you might know or read up in Excel Help or online at any time. When you type "=VOOLKUP(" Excel will give you hints, too.

  1. LookUp-Value
    In the above example it is A2, but in your task it is "Marry", except that it isn't "Marry". It's "any name containing Marry". That you can express as "*" & "Marry" & "*" where the asteric is a placeholder for any number of characters from 0 to 255 (not sure about the maximum). Therefore =VLOOKUP( "*" & "Marry" & "*" , or simply =VLOOKUP( "*Marry*" ,will consider anything a match that contains "Marry". Actually, it will also match "marry" or "MARRY" ETC.
  2. LookUp-Range
    In your example that would be A3:B50 or, better $A$3:$B$50 if you intend to copy the formula to other cells (which you don't in this case). Observe that the range excludes A2 because that is the row where you have the wrong name. If you define the range as A2:B50 the match will be found in A2. Observe that the LookUp-Range has 2 columns. VLOOKUP will only look in the first. The otehrs are for the return value. You could have many more to the right of the first. In this case you want to return a value from column B. So, you only need 2 columns.
  3. Return-Column
    In your example, you want to return the name from column B which is the second column in A3:B50. If the LookUp-Range was X3:Y50 then column 2 would define column Y. If you define a column which doesn't exist in the LookUp-Range and error will result.
  4. LookUp-Match
    You can choose between "Approximate" (TRUE) and "Exact" (FALSE). True is useful when looking for numbers, including dates. In your example the approximation is included in the LookUp-Value, and now you want an exact result.

If your formula can't find a match it will return an error. Therefore you might embed it in an IFERROR function. The IFERROR function has this description. "If my Expression returns an error return Something_Useful instead", for example, =IFERROR[Expression], [Something_Useful]). In this example, the above formula is the Expression and you can decide what is Something_Useful. Actually anything would be better than "#N/A". So, why not "not found".

So, your formula might look like this.

=IFERROR(VLOOKUP("*Marry*",A3:B50,2,FALSE), "not found")

I realise that this solves only a part of your problem. Here is a variation of the above which you should now be able to read on your own.

=IFERROR(VLOOKUP("*A2*",A3:B50,2,FALSE), B2)

As a hint, I placed the word "Marry" in A2 and "Anne-Mary" somewhere further down in column A. So, you can place the above formula in C2 and get a different result by adding an r in "Anne-Mary".

Discuss

Discussion

It's Anne-Marry only and it has to became Anne-Marry Kay. It's easy to do it with this list....to have a look on it and change it manually, but this is an exercise and I need to find a formula. Otherwise, I totally agree with you.
dex Oct 28, '17 at 12:29 am
This is "Teach Excel", not "Do your Excel home work". Count on my help - and not more. But first I need to understand: It's impossible to replace the name "Stone" with "Kay". Are you allowed to write "Kay" in column C and then delete column B?
Now I will go for lunch. When I come back I shall look for your reply. If you want to use the time to think ahead, look at the VLOOKUP function because that is what you will need. It will have to go into another column, however, because in column B you already have "Stone" and if you replace "Stone" with a formula then "Stone" will be gone. Which gives me an idea for another possible solution: just replace all the names with formulas, but then you would have to have two ranges, one with correct names and another with wrong ones. Do you have that? Or is that the required solution, to create a second corrected range? It would indeed help if you would post your workbook. You can attach it to the original question, if you like.
Variatus (rep: 4889) Oct 28, '17 at 12:48 am
The test sounds like this: "Column A = last name, column B = first name.  Search for the last name containing Marry. Correct the first name of this person to Kay."
I am a rookie regarding Excel, but I want to learn so I'm solving tests...or at least I'm trying. This isn't homework, it's just something that bothers me because I can't find a way to solve it. Here is how I tried: I selected all the names from both columns, than I pressed the function key. I was thinking to use LOOKUP for Marry in column A, after that to use IF  ...like if Marry in column A than use SUBSTITUTE   with Kay in column B. But I don't know how to connect all this functions, or if this is the correct way to do it.
dex Oct 28, '17 at 4:16 am
I deleted my original reply and replaced with something new. When you are restricted to the use of worksheet functions the substitution would have to be done by a separate process. Consider replacing the formula with its result by using Copy/Paste Special (Values) and then replacing the original column B with the modified one.
Variatus (rep: 4889) Oct 28, '17 at 6:25 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login