# Trim / Find & Add OR function somehow?

#### srosk

##### Board Regular
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

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

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Hi,

Try this:

Book1
AB
1This year's Gross profit is \$100000This year's
2Last month's Net Lost was \$1000Last month's
Sheet576
Cell Formulas
RangeFormula
B1=TRIM(LEFT(A1,MIN(SEARCH("Gross",A1&"Gross"),SEARCH("Net",A1&"Net"))-1))

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

You're welcome.

Try this updated version:

Book1
AB
1The fund raiser netted \$5000, while his year's Gross profit is \$100000The fund raiser netted \$5000, while his year's
2The project is grossing the expected profit, but last month's Net Lost was \$1000The project is grossing the expected profit, but last month's
Sheet576
Cell Formulas
RangeFormula
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.

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

 Input Output CREDIT 129 IN NET 0.40 0.47 1.40 1.35 5.27 7.13 - 1.16 CREDIT 129 IN CREDIT258 NL F GROSS 0.40 0.47 1.40 1.35 5.27 7.13 - 1.16 CREDIT258 NL F Report as net of fee Report is Gross of fee Fund or net Gross fee is net of

<tbody>
</tbody>

If the above is column A and B, I also have column C with code
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!!

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

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

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

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.

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

Replies
1
Views
248
Replies
1
Views
732
Replies
7
Views
418
Replies
3
Views
373
Replies
15
Views
260

### Forum statistics

1,203,728
Messages
6,057,012
Members
444,902
Latest member
ExerciseInFutility

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back