Extract first numerical value to the left & right of a defined criteria from a string of text Pt2

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

[TABLE="class: grid, width: 700"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]10 yr spring 399 rollers 188 sc 40 total 627 ck sc will be waived[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]$40 radio board and free sc over $500 $140 spring $103.80 40 sc total $283.80 ck [/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]argued sc reduce rate same day 5 yr 299 cables 58 rollers 148 sc 20 525 sc just because[/TD]
[/TR]
</tbody>[/TABLE]

The desired results would be:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]188,40[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD],500[/TD]
[TD]40,283.80[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD],5[/TD]
[TD]148,20[/TD]
[TD]525,[/TD]
[/TR]
</tbody>[/TABLE]


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
user-online.png

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:

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Ran a few examples and it works flawlessly .4, -40, 140, 4000

cant thank you enough

You do amazing work
You are quite welcome... this was a tough one so I am glad I was (eventually) able to provide you with a workable solution. And thank you for your kind words as well... much appreciated.
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,915
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top