Extract number(s) from A String after specific phrase to an Array

[airforceone]

from my previous problem, i somehow micro-solved it up until this problem.
from the entry below, i would like to extract the grams and insert it below corresponding cell and store the Amount of said string to array, using VBA.

i.e.
for cell B2
1.001 grams will go to B3 as 1.001
while the Amount will be stored to Array using the Branch Control Number as "KEY"

 

[eddorena]

from my previous problem, i somehow micro-solved it up until this problem.
from the entry below, i would like to extract the grams and insert it below corresponding cell and store the Amount of said string to array, using VBA.

i.e.
for cell B2
1.001 grams will go to B3 as 1.001
while the Amount will be stored to Array using the Branch Control Number as "KEY"

Hi There,

Can you send the fake sample (in case this is a credential file) that what you expected?

The question still not completely clear

 

[airforceone]

Hi There,

Can you send the fake sample (in case this is a credential file) that what you expected?

The question still not completely clear

thanks for finding time to view my problem, anyway here is what I have so far. What I'm praying is to put this formula to VBA/UDF.
sample sheet as per requested mate

00 Updating Bravo UPDATED.xlsx
	A	B	C	D	E
1	Branch Control Number	Vitamins	Vitamins	Vitamins	Vitamins
2	A00001-2022-0001	Vit A Grams : 1.001 Amount : 1	Zinc Grams : 0.1 Amount : 10	Vit D Grams : 0.01 Amount : 100	Vit D Grams : 0.0001 Amount : 1000
3		1.001	0.1	0.01	0.0001
4	A00001-2022-0002	Vit A Grams : 1.001 Amount : 1	Zinc Grams : 0.1 Amount : 10	Vit D Grams : 0.01 Amount : 100	Vit D Grams : 0.0001 Amount : 1000
5		1.001	0.1	0.01	0.0001
6
7	using this Formula I can extract the Integer Value of a String but not in VBA
8	=MID(B2,SEARCH(": ",B2)+LEN(": "),SEARCH("Amount",B2)-SEARCH(": ",B2)-LEN(": "))
Sheet1 (2)

Cell Formulas
Range	Formula
B5:E5,B3:E3	B3	=MID(B2,SEARCH(": ",B2)+LEN(": "),SEARCH("Amount",B2)-SEARCH(": ",B2)-LEN(": "))

while the amount value goes to an array

 

[NoSparks]

perhaps something along the lines of this

Function getgrams(sStr As String)
    getgrams = (Split(Split(sStr, "Grams : ")(1), " ")(0)) * 1
End Function

 

[airforceone]

perhaps something along the lines of this

Function getgrams(sStr As String)
    getgrams = (Split(Split(sStr, "Grams : ")(1), " ")(0)) * 1
End Function

good day mate, thanks for taking time to piece a code for the problem
would you be kind enough to explain how to use your function in a range loop? say we have 800 records in a range
I already have a working code, but your code seems more simpler than mine (might be faster) I really love to hear from you...

 

[airforceone]

good day mate, thanks for taking time to piece a code for the problem
would you be kind enough to explain how to use your function in a range loop? say we have 800 records in a range
I already have a working code, but your code seems more simpler than mine (might be faster) I really love to hear from you...

All is Good now!
I incorporate you code in my routines and manage to tweak it a bit now is working... for reference to others with similar problem here's my code along with yours

Cells(x, 2 + k).Value = "=MID(R[-" & xVARx & "]C,SEARCH("": "",R[-" & xVARx & "]C)+LEN("": ""),SEARCH(""Value"",R[-" & xVARx & "]C)-SEARCH("": "",R[-" & xVARx & "]C)-LEN("": ""))"

Cells(x, k + 2).Value = (Split(Split(Cells(x - 1, 2 + k), "Quantity : ")(1), " ")(0)) * 1

I seldom use split function, but after I saw your code will surely delve into it more... thanks...

 

[Peter_SSs]

If interested, you could also do it without the second Split

getgrams = Val(Split(sStr, "Grams : ")(1))

 

[airforceone]

If interested, you could also do it without the second Split

getgrams = Val(Split(sStr, "Grams : ")(1))

thanks, I'll try to incorporate it later. loop is really the culprit of sluggish code for MSExcel (VBA). will update my code later... thanks again..

 

[airforceone]

@NoSparks @Peter_SSs
down with a flu! thankfully not covid!
it was great help in its runtime aspect, from 86secs. down to 20secs both code run smoothly with a difference of some micro seconds to spare but thanks to both of you

 

[Peter_SSs]

Glad we were able to help. Thanks for letting us know.