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

[HockeyDiablo]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]10 yr spring 399 rollers 188 sc 40 total 627 ck[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]sc $40 radio board $140 spring $103.80 total $283.80 ck [/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]same day 5 yr 299 cables 58 rollers 148 sc 40 545[/TD]
[/TR]
</tbody>[/TABLE]

Above are a few samples strings from Microsoft Excel that I need the extraction from, respectively in cells "A1", "A2", "A3"...

I have several items in the string and need to extract the pricing, but it could be to the left or the right and wont always carry a $ sign.

Example: I would like to select "sc" as the defined criteria and return the first numerical value to the left and right


Return: something like this

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

I could work with values in "B" and "C"

 

[Rick Rothstein]

Runtime Error '9' Script out of range

Result(R, 1) = Result(R, 1) & ", " & Split(Trim(SC(1)))(0)

But it works when I use other data? Would a certain character throw this off??

That probably means you have the letters "sc" at the end of the text with no number following it. Change the line of code you posted to this (I marked the change in red) and see if it works correctly afterwards...

Result(R, 1) = Result(R, 1) & ", " & Split(Trim(SC(1)) & " ")(0)

 

[HockeyDiablo]

That did it, but I am getting some alpha to return:

"alex trolley was disco'ed pitched struts and told her needed new springs to compensate for added weight. $399 10 year spring $129 strut $129 strut $40 sc total$697ck" Returns 40, total697ck

" having issues with light sockets again is using 100 watt lightbulbs recommended going down to 60 and trying rough sc bulbs would still like is to check it out ~vnl chris told him to stop using 100 watt bulbs the socket still worked but it was touchy. went over bb's and cables again still no go." Returns rough, bulbs

 

[Rick Rothstein]

That did it, but I am getting some alpha to return:

"alex trolley was disco'ed pitched struts and told her needed new springs to compensate for added weight. $399 10 year spring $129 strut $129 strut $40 sc total$697ck" Returns 40, total697ck

" having issues with light sockets again is using 100 watt lightbulbs recommended going down to 60 and trying rough sc bulbs would still like is to check it out ~vnl chris told him to stop using 100 watt bulbs the socket still worked but it was touchy. went over bb's and cables again still no go." Returns rough, bulbs

What should the answers be for the above two examples?

 

[HockeyDiablo]

Well come to think of it nothing.... if both equal alpha then Service Call "sc" charge would be 0$ and I can write that.

Its always my data. Thank you again sir.

Cheers

 

[HockeyDiablo]

What if I wanted to run this same function but for letters and not isnumeric??

 

[HockeyDiablo]

place NOT before IsNumeric

whew....