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

[HockeyDiablo]

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]

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.

 

[Rick Rothstein]

There was one error in the mass of rows causing all my headaches....

That's what I thought the problem was going to be, but I wanted to see the contents of the variables I asked about to be sure (and to figure out if you needed to correct the problem or try to work around it. It looks like you have chosen to correct the problem.

What if I wanted to search for numbers and return the first words to the right/left??

Give us a few representative examples so we can see exactly what you are looking to do.

 

[HockeyDiablo]

I think the #error burned me last time, sorry I didnt check there first.

OK some examples would kind of be the opposite of what we've already covered with this:

$40 radio board and free sc over $500 $140 spring $103.80 40 sc total $283.80 ck

but instead of looking for the "sc" and returning: 40,500 ¥ 40,283.8

it would find "40" and return: ,radio ¥ spring,sc

 

[Rick Rothstein]

I think the #error burned me last time, sorry I didnt check there first.

OK some examples would kind of be the opposite of what we've already covered with this:

$40 radio board and free sc over $500 $140 spring $103.80 40 sc total $283.80 ck

but instead of looking for the "sc" and returning: 40,500 ¥ 40,283.8

it would find "40" and return: ,radio ¥ spring,sc

Okay, the 40 does not have to stand alone... fine... but what about its position within the number when it does not stand alone? Does it have to be the last digits in the number like your example ($140)? Or could it be embedded deeper, like this... $12,405.67? What about split over the decimal point like this... $12,34.05?

 

[HockeyDiablo]

Since 40 would be the pricing of an "sc" I would like 40 to be searched as 40. 140 wouldnt return the result.

I will always pull a whole number and never decimal as the pricing was fixed to the nearest whole number.

My task here is to basically find Invoiced Items from an old XML file where the return fields I need to search from are as follows:

date:8-15-14 tech james action required size of door s 16x7 model of door sold masonite door brand of opener s 1/2 chamberlain purple button work completed $13 service call $399 torsion springs 10yr warranty $188 precision rollers lifetime warranty total $500 payment method check#4038 customer notes made deal with guy cause we would have to put off door install and they will still go with new door down the road additional repair work quoted bottom seal 259 hinges bad 16 each cables bad $229 door pricing quoted se broken spring. no discussion of pricing. jb

operator 349 5yr spring 299 rollers 79 sc 40 total 767

james 1-24-14 399 10 yr 99 rollers 40 sc $538

07/18/13 - said door exploded - it is jammed and wont operate - quoted $40 plus parts no add charge for evenings - they were just on their way out so wanted asap~vnl 16x7 vinyl back. genie accelerator screw. closed on broom handle. dumped cable. I sold everything but the strut. she didn't like it b ut agreed once I got the door down I let her k now that the the top strut should get replaced. she was sad. I took 30 off just to get moving. 199 rewind 188 rollers 149 hd op bracket 99 16' strut 40 sc 675 pif cc

<tbody>
</tbody>

So these methods are the best way I can think of to get the results I need, or at least close. I didnt know of anyother way to get out what I need as each person labelled Invoiced Items differently ie: s/c, sc, serv call, service call

I have far more instances than the "sc" variable to search for but can find/replace to get them all to say "sc".
But I have cables, rollers, struts etc...

 

[Rick Rothstein]

date:8-15-14 tech james action required size of door s 16x7 model of door sold masonite door brand of opener s 1/2 chamberlain purple button work completed $13 service call $399 torsion springs 10yr warranty $188 precision rollers lifetime warranty total $500 payment method check#4038 customer notes made deal with guy cause we would have to put off door install and they will still go with new door down the road additional repair work quoted bottom seal 259 hinges bad 16 each cables bad $229 door pricing quoted se broken spring. no discussion of pricing. jb

operator 349 5yr spring 299 rollers 79 sc 40 total 767

james 1-24-14 399 10 yr 99 rollers 40 sc $538

07/18/13 - said door exploded - it is jammed and wont operate - quoted $40 plus parts no add charge for evenings - they were just on their way out so wanted asap~vnl 16x7 vinyl back. genie accelerator screw. closed on broom handle. dumped cable. I sold everything but the strut. she didn't like it b ut agreed once I got the door down I let her k now that the the top strut should get replaced. she was sad. I took 30 off just to get moving. 199 rewind 188 rollers 149 hd op bracket 99 16' strut 40 sc 675 pif cc

<tbody>
</tbody>

Are the above in one cell or four separate cells?

Can you show me the answer you want for the above example(s)?

 

[HockeyDiablo]

[TABLE="class: cms_table"]
<tbody>[TR]
[TD]date:8-15-14 tech james action required size of door s 16x7 model of door sold masonite door brand of opener s 1/2 chamberlain purple button work completed $13 service call $399 torsion springs 10yr warranty $188 precision rollers lifetime warranty total $500 payment method check#4038 customer notes made deal with guy cause we would have to put off door install and they will still go with new door down the road additional repair work quoted bottom seal 259 hinges bad 16 each cables bad $229 door pricing quoted se broken spring. no discussion of pricing. jb: No Instance of 40, No Return[/TD]
[/TR]
[TR]
[TD]operator 349 5yr spring 299 rollers 79 sc 40 total 767: sc,total[/TD]
[/TR]
[TR]
[TD]james 1-24-14 399 10 yr 99 rollers 40 sc $538: rollers,sc[/TD]
[/TR]
[TR]
[TD]07/18/13 - said door exploded - it is jammed and wont operate - quoted $40 plus parts no add charge for evenings - they were just on their way out so wanted asap~vnl 16x7 vinyl back. genie accelerator screw. closed on broom handle. dumped cable. I sold everything but the strut. she didn't like it b ut agreed once I got the door down I let her k now that the the top strut should get replaced. she was sad. I took 30 off just to get moving. 199 rewind 188 rollers 149 hd op bracket 99 16' strut 40 sc 675 pif cc: quoted,plus [next column] strut,sc[/TD]
[/TR]
</tbody>[/TABLE]

Something to this effect...

 

[Rick Rothstein]

[TABLE="class: cms_table"]
<tbody>[TR]
[TD]date:8-15-14 tech james action required size of door s 16x7 model of door sold masonite door brand of opener s 1/2 chamberlain purple button work completed $13 service call $399 torsion springs 10yr warranty $188 precision rollers lifetime warranty total $500 payment method check#4038 customer notes made deal with guy cause we would have to put off door install and they will still go with new door down the road additional repair work quoted bottom seal 259 hinges bad 16 each cables bad $229 door pricing quoted se broken spring. no discussion of pricing. jb: No Instance of 40, No Return[/TD]
[/TR]
[TR]
[TD]operator 349 5yr spring 299 rollers 79 sc 40 total 767: sc,total[/TD]
[/TR]
[TR]
[TD]james 1-24-14 399 10 yr 99 rollers 40 sc $538: rollers,sc[/TD]
[/TR]
[TR]
[TD]07/18/13 - said door exploded - it is jammed and wont operate - quoted $40 plus parts no add charge for evenings - they were just on their way out so wanted asap~vnl 16x7 vinyl back. genie accelerator screw. closed on broom handle. dumped cable. I sold everything but the strut. she didn't like it b ut agreed once I got the door down I let her k now that the the top strut should get replaced. she was sad. I took 30 off just to get moving. 199 rewind 188 rollers 149 hd op bracket 99 16' strut 40 sc 675 pif cc: quoted,plus [next column] strut,sc[/TD]
[/TR]
</tbody>[/TABLE]

Something to this effect...

Okay, one last question (I think )... will there ever be a number next to the 40 and, if so, how would it be handled. I am thinking something like this...

text text word1 123 40 word2 text text

or even...

text text word1 123 40 567 word2 text text

Would the return for these be word1,word2?

 

[HockeyDiablo]

Yes Sir, I am just looking for the words to the Right/Left. Some "40" values may/maynot have numbers next to them. Here is the closest example I could quickly find.

bigsprings 599 gear and sprocket for ats 189 cables 8ft 129 sc 40 956 .430 42.5

sc,

 

[Rick Rothstein]

Yes Sir, I am just looking for the words to the Right/Left. Some "40" values may/maynot have numbers next to them. Here is the closest example I could quickly find.

bigsprings 599 gear and sprocket for ats 189 cables 8ft 129 sc 40 956 .430 42.5 SomeWord

sc,

If your line of text include text after those numbers (as shown in red above), you would not want the text reported because it was not adjacent to the number 40, is that correct?

 

[HockeyDiablo]

I would like to return what ever word is to the right or left of the desired search value "number", regardless if there are numbers after/before the "number"

"40"
bigsprings 599 gear and sprocket for ats 189 cables 8ft 129 sc 40 956 .430 42.5 SomeWord: sc,SomeWord
bigsprings 599 gear and sprocket for ats 189 cables 8ft 129 sc 40 956 .430 42.5: sc,