copying specific text from one cell and pasting in the next column, same row « Chandoo.org - Learn Excel & Charting Online

Chandoo.org - Learn Excel & Charting Online - Forums » Ask an Excel Question

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

(7 posts) (3 voices)

Started 2 years ago by labasta
Latest reply from xld

Tags:

labasta

Member
Joined: Apr '11
Posts: 2

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?

Posted 2 years ago #
Hui

Excel Ninja
Joined: Jul '09
Posts: 5,040

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

Posted 2 years ago #
Hui

Excel Ninja
Joined: Jul '09
Posts: 5,040
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 )
======
```
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
```
Posted 2 years ago #
labasta

Member
Joined: Apr '11
Posts: 2

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.

Posted 2 years ago #
xld

Member
Joined: Sep '10
Posts: 233

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

Posted 2 years ago #
Hui

Excel Ninja
Joined: Jul '09
Posts: 5,040

@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.

Posted 2 years ago #
xld

Member
Joined: Sep '10
Posts: 233

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

Posted 2 years ago #