Extracting number after a specific word, copying it and after paste it new sheet

[siddhjain91]

Hi

Lately I have been writing a code for a tool. I am stuck at a point and wanted some help.

Data in Sheet1 appears as following

Column1

Row1 if abc eq 4397324

or abc eq 427478 or

or abc eq 578483


Row2 if abc eq 12345

or abc eq 98835


Row3 if abc eq 958373


Now I want to write a code such that in Sheet2 we get 4397324(cell(1,1)), 427478 (Cell(2,1)), 578483 (cell 3,1)) and so on.

Thanks in advance.

 

[Luke M]

This should help get you started.

[pre]

Sub Transfer()
Dim MyRange As Range
Dim c As Range
Dim MyString As String
Dim MyValue As String
Dim SpotFound As Integer
Dim i As Long
Dim SearchWord As String

SearchWord = "eq "
Set MyRange = Worksheets("Sheet1").Range("A1:A3")
i = 1

With Worksheets("Sheet2")
Application.ScreenUpdating = True
For Each c In MyRange
MyString = c.Value
SpotFound = 0
On Error Resume Next
SpotFound = WorksheetFunction.Find(SearchWord, MyString)
On Error GoTo 0
If SpotFound > 0 Then
MyValue = Mid(MyString, SpotFound + Len(SearchWord))
Else
MyValue = 0
End If
.Cells(i, 1).Value = MyValue
i = i + 1
Next c
End With
Application.ScreenUpdating = True

End Sub

[/pre]

 

[siddhjain91]

Hi Luke

Thanks for your post

Although I have encountered an issue with this

As I mentioned earlier if I cell has three numbers separated by "or eeid eq" then I wanted the three numbers in three successive rows in cell(1,1), cell(2,1) and cell(3,1) and then reach for next cell in inputdata sheet. But this does not separate them in three rows rather it provides data in following way

Column1

4356 or eeid eq 33646 or eeid eq 53535

4356 or eeid eq 33646

4356 or eeid eq 33646

4356

4356


Can you please assist me in this


Thanks in Advance

 

[Luke M]

Assuming above input that you just stated, this code will output a list of numbers.

[pre]

Sub Transfer()
Dim MyRange As Range
Dim c As Range
Dim MyString As String
Dim MyValue As String
Dim SpotFound
Dim i As Long

Const SearchWord = "or eeid eq"

Set MyRange = Worksheets("Sheet1").Range("A1:A5")
i = 1

With Worksheets("Sheet2")
Application.ScreenUpdating = True
For Each c In MyRange
SpotFound = Split(c.Value, SearchWord)
For x = 0 To UBound(SpotFound)
.Cells(i, 1).Value = SpotFound(x)
i = i + 1
Next x
Next c
End With
Application.ScreenUpdating = True

End Sub

[/pre]