Find Value from a Cell in String and return the word

[ivanmcgurk21]

Hello,

I have done some searching but unfortunately I cannot find a solution.

I need to be able to find and return a word from a string, based on the value of a given cell so I can include the returned word as part of another code. Please see below for a basic example of what I am trying to do.

	a	b	c
1	AAE	ASD EDR AAE	EXTRACT AAE FROM STRING & RETURN HERE<extract aae="" from="" b1="" &="" return="" here=""></extract>

<tbody>
</tbody>

<return aae="" from="" string="" here=""><return aae="" here="">The string I have included in 'b' in reality is in another sheet, so I need to be able to identify if the value is in the string so I can copy that row.

Thanks in advance.</return></return>

 

[Aryatect]

Hi,

Are you looking for something like below:

	A	B	C
1	AAE	ASD EDR AAE	AAE
2	AAE	ASD EDR ASE	ERROR TEXT

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula C1 =IFERROR(MID(B1,SEARCH(A1,B1),LEN(A1)),"ERROR TEXT") C2 =IFERROR(MID(B2,SEARCH(A2,B2),LEN(A2)),"ERROR TEXT") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[ivanmcgurk21]

Hello,

Thanks for the quick reply. Sorry, I should have said that I'm trying to work this out in VBA.

I basically have one sheet with a list of single codes and another where cells may contain multiple codes. I need to be able to identify if a cell with multiple codes includes the code shown in a given cell. If the code is found within that string then copy that row into another sheet.

 

[ivanmcgurk21]

I have managed to get a code that does copy the rows in, but it copies all rows and not just the ones that meet the criteria. I think there is an issue in the for loop, can anyone help me out here?

Private Sub CommandButton1_Click()


Dim xRg As Range
Dim xCell As Range
Dim I As Long
Dim J As Long
Dim K As Long
I = Worksheets("MASTER LIST").UsedRange.Rows.Count
J = Worksheets("VALIDATED LIST").UsedRange.Rows.Count
If J = 1 Then
If Application.WorksheetFunction.CountA(Worksheets("VALIDATED LIST").UsedRange) = 0 Then J = 0
End If
Set xRg = Worksheets("MASTER LIST").Range("E1:E" & I)
On Error Resume Next
Application.ScreenUpdating = True

For K = 1 To xRg.Count
If CStr(xRg(K).Value) = InStr(Worksheets("MASTER LIST").Range("E1:E" & I).Value, Worksheets("TRANSPOSED DATA NO SPACES").Range("B1:B" & J)) > 1 Then
xRg(K).EntireRow.Copy Destination:=Worksheets("VALIDATED LIST").Range("A" & J + 1)
J = J + 1
End If
Next
Application.ScreenUpdating = True
End Sub