HockeyDiablo
Board Regular
- Joined
- Apr 1, 2016
- Messages
- 182
This is an additional question from post.
http://www.mrexcel.com/forum/excel-...left-right-defined-criteria-string-text.html#
I am curious on how to get the program to return additional columns if there is another instance of criteria
<tbody>
</tbody>
The desired results would be:
<tbody>
</tbody>
TY to: Rick Rothstein
MrExcel MVP
I am basically looking to extract numbers to the right/left when a criteria is matched. But I want to add something where if there is a second instance of the criteria, to place that in the next respective column.
http://www.mrexcel.com/forum/excel-...left-right-defined-criteria-string-text.html#
I am curious on how to get the program to return additional columns if there is another instance of criteria
A | |
1 | 10 yr spring 399 rollers 188 sc 40 total 627 ck sc will be waived |
2 | $40 radio board and free sc over $500 $140 spring $103.80 40 sc total $283.80 ck |
3 | argued sc reduce rate same day 5 yr 299 cables 58 rollers 148 sc 20 525 sc just because |
<tbody>
</tbody>
The desired results would be:
A | B | C | |
1 | 188,40 | ||
2 | ,500 | 40,283.80 | |
3 | ,5 | 148,20 | 525, |
<tbody>
</tbody>
Code:
Sub NumsToRightAndLeftOf_sc() 'Rick Rothstein MrExcel MVP Dim R As Long, X As Long, Criteria As String, S() As String
Dim Data As Variant, SC As Variant, Result As Variant
Criteria = "sc"
Data = Range("A1", Cells(Rows.Count, "A").End(xlUp))
ReDim Result(1 To UBound(Data), 1 To 1)
For R = 1 To UBound(Data)
SC = Split(" " & Replace(Data(R, 1), "$", "") & " ", " " & Criteria & " ", , vbTextCompare)
If UBound(SC) > 0 Then
S = Split(Trim(SC(0)))
If UBound(S) = -1 Then Result(R, 1) = "" Else Result(R, 1) = S(UBound(S))
Result(R, 1) = Result(R, 1) & ", " & Split(Trim(SC(1)) & " ")(0)
End If
Next
Range("B1").Resize(UBound(Result)) = Result
End Sub
TY to: Rick Rothstein
MrExcel MVP
I am basically looking to extract numbers to the right/left when a criteria is matched. But I want to add something where if there is a second instance of the criteria, to place that in the next respective column.
Last edited: