Trim / Find & Add OR function somehow?

[srosk]

Hi there. I have the following code, looking for all text to the left of "Gross". Is there a way to update this to show text to the left of either "Gross" or "Net"?

Thank you

=TRIM(LEFT(A2,MIN(FIND({"GROSS"},A2&"GROSS"))-1))

 

[srosk]

Yes, but in situation where a number follows Net / Gross, or other words follow like "Report is Gross of fee Fund or net."

 

[jtakw]

See if this works for you:

Book1
	A	B
4	Input	Output
5	CREDIT 129 IN NET 0.40 0.47 1.40 1.35 5.27 7.13 - 1.16	CREDIT 129 IN
6	CREDIT258 NL F GROSS 0.40 0.47 1.40 1.35 5.27 7.13 - 1.16	CREDIT258 NL F
7	Report as net of fee
8	Report is Gross of fee Fund or net
9	Gross fee is net of
Sheet576

Cell Formulas
Range	Formula
B5	=IF(MIN(FIND({0,1,2,3,4}+{0;5},A5&1/17))>LEN(A5),"",TRIM(LEFT(A5,MIN(SEARCH(" Gross "," "&A5&" Gross "),SEARCH(" Net "," "&A5&" Net "))-1)))

 

[srosk]

This is so cool. Everyone is awesome. Thank you !!!!

 

[jtakw]

You're welcome, thanks for the feedback.

 

[srosk]

You're welcome, thanks for the feedback.

Hey there. Was doing some testing - what exactly does this part of the code do?

[COLOR=#333333]IF([/COLOR][COLOR=Blue]MIN([COLOR=Red]FIND([COLOR=Green]{0,1,2,3,4}+{0;5},A5&1/17[/COLOR])[/COLOR])>LEN([COLOR=Red]A5[/COLOR]),"",[/COLOR]

It seems like the code is requiring value to the left of NET / GROSS to have a number in it. Is that what the above is doing? When I just use the following, it seems to give proper results, but wanted to confirm with you first. Thank you again for your help.

[COLOR=Blue]TRIM([COLOR=Red]LEFT([COLOR=Green]A5,MIN([COLOR=Purple]SEARCH([COLOR=Teal]" Gross "," "&A5&" Gross "[/COLOR]),SEARCH([COLOR=Teal]" Net "," "&A5&" Net "[/COLOR])[/COLOR])-1[/COLOR])[/COLOR])[/COLOR]

 

[jtakw]

The first part of my formula checks to see if there are Numbers within the Text string, if there isn't (meaning it's just a sentence), returns Blank, as per your request in Post # 7.

Without that part of the forumula, it would return Incorrect results for rows 7 & 8 for the sample in Post # 12.

 

[srosk]

Oh ok, makes sense. Is it possible for the formula to search the whole line of text? Currently, it seems to be requiring a number to the left of NET/GROSS, even if there is a number to the right of NET/GROSS, if that makes sense?

CREDIT 129 IN NET 0.40 0.47 1.40 1.35 5.27 7.13 - 1.16

 

[jtakw]

My formula checks the Entire string for numbers, Not just to the left of Net/Gross.

 

[srosk]

Oh. hmm, must've copied the formula down wrong, works now

 

[jtakw]

Glad it's working for you.