PDA

View Full Version : Find particular text in a sentence in a cell and copy to adjacent cell



Kumarcoolz
10-06-2014, 04:21 AM
Hi Guys,
Need help with a small problem. I am trying to get a particular format in a sentence.

sample sentence in a cell
Not more than 13,00mm in depth
Required output in adjacent cell
13,00mm

or

sample sentence in a cell
Not more than 13,00mm in length and 0,5mm in depth
Required output in adjacent cell
13,00mm and 0,5mm


i am not sure if this is possible. but any help or directions will be greatly helpfulBest Regards
Kumar

ranman256
10-06-2014, 05:31 AM
Sub FindTxt()
Dim vFind
vFind = InputBox("Enter Search term", "Find")
Range("A2").Select
While ActiveCell.Value <> ""
If InStr(ActiveCell.Value, vFind) > 0 Then ActiveCell.Offset(0, 1).Value = ActiveCell.Value
ActiveCell.Offset(1, 0).Select 'next row
Wend
End Sub

Kumarcoolz
10-06-2014, 06:02 AM
Thanks ranman256

but the code doesnt work in my favour :(. i am looking for an approach that will pull out the full text with the matching words rather than the entire sentence

sample sentence in a cell
Not more than 13,00mm in depth
Required output in adjacent cell
13,00mm


i am trying to get all the texts containing the mm in the suffix in the sentence


Thanks again
Kumar






Sub FindTxt()
Dim vFind
vFind = InputBox("Enter Search term", "Find")
Range("A2").Select
While ActiveCell.Value <> ""
If InStr(ActiveCell.Value, vFind) > 0 Then ActiveCell.Offset(0, 1).Value = ActiveCell.Value
ActiveCell.Offset(1, 0).Select 'next row
Wend
End Sub

jolivanes
10-08-2014, 09:22 AM
Assuming data starts in A1. Change accordingly.


Function get_mm(c00)
get_mm = Join(Filter(Split(Replace(c00 & " ", " ", " |"), "|"), "mm "), " and ")
End Function



Sub snb()
With Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row).Offset(, 1)
.Formula = "=get_mm(RC[-1])"
.Value = .Value
End With
End Sub


It was not me who did this but the gentleman at this site.


http://www.worksheet.nl/forumexcel/showthread.php?t=86718&p=694776#post694776

Aussiebear
10-08-2014, 01:06 PM
Hmmm... that has all the hall marks of snb.

jolivanes
10-08-2014, 03:28 PM
Hey Aussiebear.
That is very obvious I think with the code re-named "Sub snb()" and the hyperlink.
I would not dare to make people think that I was that smart.
The reason I put it in that forum was because I tried for a couple of days to come up with something reasonable. I failed miserably.
As most of us have seen, snb comes up with these more or less one liners that do the trick so I hoped he would fall for my devious plan and indeed he did.
A lot of respect for him as well as you guys that come up with some, in my eyes, fantastic code.
I don't think I overstepped boundaries here, did I?
Regards.
John

This was my attempt

Sub Like_This_Maybe()
Dim jve, c As Range, j As Long
For Each c In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
jve = Split([c], " ")

For j = LBound(jve) To UBound(jve)
If IsNumeric(Left(jve(j), 1)) And Right(jve(j), 2) = "mm" Then c.Offset(, 1).Value = c.Offset(, 1).Value & " " & jve(j)
Next j
Next c
For Each c In Range("B1:B" & Cells(Rows.Count, 2).End(xlUp).Row)
c.Value = Trim(c)
c.Value = Replace(c, " ", " and ")
Next c
End Sub