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))

 

[jtakw]

Hi,

Try this:

Book1
	A	B
1	This year's Gross profit is $100000	This year's
2	Last month's Net Lost was $1000	Last month's
Sheet576

Cell Formulas
Range	Formula
B1	=TRIM(LEFT(A1,MIN(SEARCH("Gross",A1&"Gross"),SEARCH("Net",A1&"Net"))-1))

 

[srosk]

This is awesome, thank you very much!


Not sure if you can help further or not but! I tried the code and results look good. The only issue is that it sometimes flags irrelevant text since it contains the word net / gross. Can we add a requirement that it is the word net or gross, followed by a space and either a - sign or number 0-9

GROSS -0.10
GROSS 1.02
GROSS 8.38
GROSS -8.38
GROSS 0.45

Any idea? Thank you again

 

[jtakw]

You're welcome.

Try this updated version:

Book1
	A	B
1	The fund raiser netted $5000, while his year's Gross profit is $100000	The fund raiser netted $5000, while his year's
2	The project is grossing the expected profit, but last month's Net Lost was $1000	The project is grossing the expected profit, but last month's
Sheet576

Cell Formulas
Range	Formula
B1	=TRIM(LEFT(A1,MIN(SEARCH(" Gross "," "&A1&" Gross "),SEARCH(" Net "," "&A1&" Net "))-1))

If you may have strings that do Not contain "Gross" or "Net", and you want it Flagged, let me know.

 

[srosk]

Thanks! I'm not sure that did the correct output. Let me show sample data of what I'm looking for.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Input
[/TD]
[TD]Output
[/TD]
[/TR]
[TR]
[TD]CREDIT 129 IN NET 0.40 0.47 1.40 1.35 5.27 7.13 - 1.16
[/TD]
[TD]CREDIT 129 IN
[/TD]
[/TR]
[TR]
[TD]CREDIT258 NL F GROSS 0.40 0.47 1.40 1.35 5.27 7.13 - 1.16
[/TD]
[TD]CREDIT258 NL F
[/TD]
[/TR]
[TR]
[TD]Report as net of fee
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Report is Gross of fee Fund or net
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Gross fee is net of
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

If the above is column A and B, I also have column C with code

=TRIM(REPLACE(A52,1,LEN(B52),""))

what this does is copies the input, delete what is in column B... gives me everything from NET/GROSS onward.

Not sure if I am conveying this properly... let me know if this makes any more sense than it did before lol.

Thank you again!!

 

[jtakw]

It looks like you already has a working formula for Column C.
What exactly do you need help with ?

Here's another formula for Column C, does the same job as the one you already have:

=TRIM(SUBSTITUTE(A52,B52,""))

 

[srosk]

Is there a way for there to be no output if it is just a sentence and not a row of returns? Like in my sample

 

[jtakw]

How are we to tell the difference between a plain sentence and otherwise, no numbers ?

 

[srosk]

Correct. The word net or gross should be followed by either a number 0-9, or the negative sign. Like this:

GROSS -0.10
GROSS 1.02
GROSS 8.38
GROSS -8.38
GROSS 0.45

If not, then it just shows blank.

 

[jtakw]

Looking at your samples, will there Always be a decimal somewhere After Gross or Net ?