if cell includes string, append a character to the value of another cell.

**robbyvegas** · 06-29-2012, 04:35 PM

Hi guys and gals,

I need a bit of help from the excel gurus.

I'd written a formula that would check the value of each cell in a column (P2:...) and if the cell includes specific text ("May 29") it sets the value of another cell (F2:...). Here's what I wrote:

=IF(OR(NOT(ISERROR(FIND({"May 29"},P2)))), "EPDI","")

Now I need to be able to do basically the same thing, only there is already a set of information in the column (F), so I need to append the value to the cell (F2:...) if (P2:...) contains the value that I'm looking for. If P2... doesn't contain the value that I'm looking for, I want to let the value of F2... remain.

The other kink is that I will likely have to do this again in the future, or have someone else do it, so a macro that will ask me to identify the column to search, the text to find, the target column, and the text to append, would be really really handy.

I'm working on learning my VBA skills, but this is completely out of my league. Can anyone help me out?

Thanks!

Rob

**oeldere** · 06-29-2012, 04:39 PM

You get better help if you post an Excel-example of your workbook, without confidential information.

Make sure the workbook demonstrates your desired results if possible, or just highlight the cells you're trying to fix.

Use BEFORE/AFTER sheets if that helps make it clearer.

**robbyvegas** · 06-29-2012, 06:04 PM

A fair point Oeldere. Thank you! Teach me to be in a hurry

Here is an example of the spreadsheet before applying the macro:
Before Sample.xlsx

And here is an example of the spreadsheet after applying the macro:
After Sample.xlsx

In this example upon executing the macro, I would be prompted with a message box asking me to identify the column to search.
I selected or entered column H
A text box would then ask me to enter the word to find. I would enter "April 13,"
Another message box would then ask me to identify the target column.
I would select column B
Finally, a message asks me to enter the text to be appended.
I would enter "; Armenian"
the macro would search for the text "April 13," in each cell.
Finding "April 13," in H2, the macro would append the string "French; German; English" at B2 with the term "; Armenian" to create "French; German; English; Armenian"
The macro would then move onto H3, and would create "French; Armenian" in B3
At row 4, the system would just enter "Armenian" (I know, the "; "... just thought of that and don't know how to deal with it. Any brilliant ideas?)
At row 5, because "April 13," does not appear at H5, B5 remains "Klingon, Unicorn" but no "; Armenian"
And so on...

This example brings up another completely different concern. Notice that the person who came before me wasn't very consistent. Sometimes they used ";" and other times they used ",". There are also random carriage returns and extra trailing spaces in different cells. Any idea how to consistently get rid of these?

Sorry if that is too long and drawn out, or if it put anyone to sleep. I really appreciate the help on this. I'm trying to learn, but like I said, this is way out of my league right now.

Thanks,
Rob

**oeldere** · 06-30-2012, 04:16 AM

Before looking ad your examples:

1) I can't help you enough with VBA.

Sometimes they used ";" and other times they used ",". There are also random carriage returns and extra trailing spaces in different cells

2) Work with datavalidation

3) For the future, you can use the same workbook for the before an after situation.
You can use differnt worksheets.

i attach a workbook with the before an after situation.

Maybe you can point us, where we can find the cells that need to be changed.

**jaslake** · 06-30-2012, 03:13 PM

Hi robbyvegas
Try the code in the attached. I believe it addresses these issues

I would be prompted with a message box asking me to identify the column to search

ask me to enter the word to find

ask me to identify the target column

asks me to enter the text to be appended

Sometimes they used ";" and other times they used ",". There are also random carriage returns and extra trailing spaces in different cells. Any idea how to consistently get rid of these?

Let me know of issues.

**JapanDave** · 06-30-2012, 04:46 AM

Try this,

Click the button to the right to run.

**robbyvegas** · 07-04-2012, 12:08 AM

Originally Posted by JapanDave

Try this,

Click the button to the right to run.

You guys ROCK! Sorry, I haven't had a chance to check in since I lots power on Fri. night. My house is still without power... Argh...

I'll try these solutions now.

Thanks again!

**Marcol** · 06-30-2012, 06:05 AM

You could just use native fumctions and standard Excel features, they are worth learning!

!/. Add a sheet, I've called it "Before Cleaned"
In A1

=TRIM(SUBSTITUTE(Before!A1,";",","))

Drag Across and Down as required
Copy the result, (not the whole sheet), and Paste Special > Values to
"Report Language" A2

Delete Sheets "Before" & "Before Cleaned"

2/. With "Report Language"
Insert a column between Columns B:C, this will become Column C, the following columns will increment automatically.

In A1 put the language you need to add "Armenian"

In B2 put the text you want to find "April 13", this should be preceded with an apostrophe to avoid Excel forcing a serial date.

In C3

=IF(ISNUMBER(SEARCH($B$1,INDEX($A:$I,ROWS($1:2),MATCH("Reports",$A$2:$I$2,0)))),IF(B3="",$A$1,B3&", "&$A$1),B3)

Drag/Fill Down as required.

You can then use Copy > Paste Special > Values and delete columns as you need.

This takes longer to explain than do!

if cell includes string, append a character to the value of another cell.

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

if cell includes string, append a character to the value of another cell.

Re: if cell includes string, append a character to the value of another cell.

Re: if cell includes string, append a character to the value of another cell.

Re: if cell includes string, append a character to the value of another cell.

Re: if cell includes string, append a character to the value of another cell.

Re: if cell includes string, append a character to the value of another cell.

Re: if cell includes string, append a character to the value of another cell.

Re: if cell includes string, append a character to the value of another cell.

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions