Help with UDF syntax and parsing

[JenniferMurphy]

I want to write a little function to compare the amount of sodium in several products. The data is currently in a text string of the form: Xg Ymg, where X is one serving of the product and Y is the amount of sodium in that serving. I need to extract the X and Y then calculate Y/X. The text string might also be blank. Here's some sample data:

Book1
	C	D
4	28.35g 0mg	0.00
5	28.35g 0mg	0.00
6	28g 135mg	4.82
7
8	28g 135mg	4.82
9	28g 135mg	4.82
10	28g 65mg	2.32
11
12	28g 110mg	3.93
Sheet1

Cell Formulas
Range	Formula
D4:D5	D4	=0/28.35
D6,D8:D9	D6	=135/28
D10	D10	=65/28
D12	D12	=110/28

Is there a simple way to extract those values in VBA?

Is there a simple Excel expression that will extract the two numerical values and pass them to the UDF? I suppose I'd have to make the parameters optional in order to be able to handle the blank case.

 

[Legacy 143009]

This will work if the units always going to be g and mg.

=LET(s,SEARCH(" ",A1),r,RIGHT(A1,LEN(A1)-s),LEFT(r,LEN(r)-2)/LEFT(A1,s-2))

 

[JenniferMurphy]

This will work if the units always going to be g and mg.

=LET(s,SEARCH(" ",A1),r,RIGHT(A1,LEN(A1)-s),LEFT(r,LEN(r)-2)/LEFT(A1,s-2))

Wow! It works. I'll have to study it to understand how. It does get a value error on the blank rows, but that's not a serious problem, so I've marked this as solved.

Thank you

Book1
	C	D	E
4	28.35g 0mg	0.00	0.00
5	28.35g 0mg	0.00	0.00
6	28g 135mg	4.82	4.82
7			#VALUE!
8	28g 135mg	4.82	4.82
9	28g 135mg	4.82	4.82
10	28g 65mg	2.32	2.32
11			#VALUE!
12	28g 110mg	3.93	3.93
Sheet1

Cell Formulas
Range	Formula
D4:D5	D4	=0/28.35
D6,D8:D9	D6	=135/28
D10	D10	=65/28
E4:E12	E4	=LET(s,SEARCH(" ",C4),r,RIGHT(C4,LEN(C4)-s),LEFT(r,LEN(r)-2)/LEFT(C4,s-2))
D12	D12	=110/28

 

[Legacy 143009]

Glad it did work You can overcome the error problem simply with an IFERROR check:

=IFERROR(LET(s,SEARCH(" ",A1),r,RIGHT(A1,LEN(A1)-s),LEFT(r,LEN(r)-2)/LEFT(A1,s-2)),"")

First, the formula finds the position space and takes the right text until the space position by substracting it form the text length. Then, trims the last two characters "mg" from the end.
Second, it finds the position of space again but this time takes the left text until the space. And trims two characters again "g " form the end.
Then divides them.

 

[JenniferMurphy]

Glad it did work You can overcome the error problem simply with an IFERROR check:

=IFERROR(LET(s,SEARCH(" ",A1),r,RIGHT(A1,LEN(A1)-s),LEFT(r,LEN(r)-2)/LEFT(A1,s-2)),"")

First, the formula finds the position space and takes the right text until the space position by substracting it form the text length. Then, trims the last two characters "mg" from the end.
Second, it finds the position of space again but this time takes the left text until the space. And trims two characters again "g " form the end.
Then divides them.

Even better and I think I can actually follow the logic!

I moved the solution to this message.

Thank you very much!

 

[Legacy 143009]

Thanks for the feedback

 

[Dave Patton]

with Excel 365, try the following:

Let_a.xlsm
	A	B	C	D
1				Excel
2				365
3	28.35g 0mg	0	0	0
4	28g 135mg	4.821428571	4.821429	4.821429
5	28g 65mg	2.321428571	2.321429	2.321429
6	28g 110mg	3.928571429	3.928571	3.928571
4f

Cell Formulas
Range	Formula
B3	B3	=0/28.35
C3:C6	C3	=IFERROR(LET(s,SEARCH(" ",A3),r,RIGHT(A3,LEN(A3)-s),LEFT(r,LEN(r)-2)/LEFT(A3,s-2)),"")
D3:D6	D3	=IFERROR(TEXTBEFORE(TEXTAFTER(A3,"g"),"m")/TEXTBEFORE(A3,"g"),"")
B4	B4	=135/28
B5	B5	=65/28
B6	B6	=110/28

 

[JenniferMurphy]

with Excel 365, try the following:

Let_a.xlsm
	A	B	C	D
1				Excel
2				365
3	28.35g 0mg	0	0	0
4	28g 135mg	4.821428571	4.821429	4.821429
5	28g 65mg	2.321428571	2.321429	2.321429
6	28g 110mg	3.928571429	3.928571	3.928571
4f

Cell Formulas
Range	Formula
B3	B3	=0/28.35
C3:C6	C3	=IFERROR(LET(s,SEARCH(" ",A3),r,RIGHT(A3,LEN(A3)-s),LEFT(r,LEN(r)-2)/LEFT(A3,s-2)),"")
D3:D6	D3	=IFERROR(TEXTBEFORE(TEXTAFTER(A3,"g"),"m")/TEXTBEFORE(A3,"g"),"")
B4	B4	=135/28
B5	B5	=65/28
B6	B6	=110/28

Impressive. That is simpler and cleaner. I think I need to get a book on new features in Excel 365.

Thanks for adding that.

 

[Dave Patton]

Thanks for the feedback.