copying specific text from one cell and pasting in the next column, same row

[labasta]

I've been emailing this around a few excel add-in sellers on the net, but I'm not sure it can be done. If not excel, would another programme do it? I know Regex language could do it.


I'm willing to pay for this customization if it can be done. Here is the email I sent around:


What I need is software to copy (not extract) text from excel cells and add

this text to another cell (the next column along). I need to be able to

enter the words which the software is to copy and paste.


Example:


*dog training is good

*cat was walking down the street

*parrot made a sound and ran away

*hamster ran away

*mouse made a squeaking sound

*gerbil ran under the floor without making a sound


Let's say I have the above text in each cell in excel.


Let's say I want to copy and paste the following words:


ran

sound


Excel then pastes these words in the next column in the same row in the same

order it finds them. It would do this:


*dog training is good.

*cat was walking down the street.

*parrot made a sound and ran away. sound ran

*hamster ran away. ran

*mouse made a squeaking sound. sound

*gerbil ran under the floor without making a sound. ran sound


Do you see what I mean?


Can this be done?

 

[Hui]

Assuming your list is in Column A2:A7

and the words Ran and Sound are in B1 & C1

Copy this to B2

=A2&IF(ISERROR(SEARCH($B$1,A2)>0),""," "&$B$1)&IF(ISERROR(SEARCH($C$1,A2)>0),""," " &$C$1)

and copy down

 

[Hui]

Now I am sure your list is longer than the 2 words, Sound and Ran


This is better handled with a User Defind Function (UDF)

Copy and paste the text below the === into a code module in VBA (Alt F11)


To use, enter the formula on your worksheet

=Included_Add(A2,$B$1:$C$1)

Where A2 is your text and $B$1:$C$1 is your list of words

This is case sensitive


The Optional Case parameter


This will Ignore Case

=Included_Add(A2, $B$1:$C$1, 1)

This will enforce Case

=Included_Add(A2, $B$1:$C$1, 0) or =Included_Add(A2, $B$1:$C$1 )

======

[pre]

Function Included_Add(Src_Rng As Range, Wrd_Rng As Range, Optional myCase = 0) As Variant
Dim w As Range
Dim cstring As String
For Each w In Wrd_Rng
If myCase = 1 Then
If InStr(1, UCase(Src_Rng.Value), UCase(w.Value)) > 1 Then cstring = cstring & " " & w.Value
Else
If InStr(1, Src_Rng.Value, w.Value) > 1 Then cstring = cstring & " " & w.Value
End If
Next
Included_Add = Src_Rng + cstring
End Function

[/pre]

 

[labasta]

Thanks everyone for your prompt replies and serious effort in helping me with my problem. I very much appreciate what everyone does here.


However, I got lucky and someone on excelguru gave me a script he already had (at least I think so) which has solved my problem perfectly.


Thanks for all the replies though.

 

[xld]

This guy has cross-posted this at ExcelGuru http://bit.ly/em05qF

 

[Hui]

@XLD, Thanx for the heads up


I really enjoy wasting my time on these type of problems


Another person added to the don't respond to list.

 

[xld]

Exactly Hui, I despair of the self-centredness of such people thinking we are all just here to be at their beck and call.