ADJUST "LEFT(FIND" IN FORMULA TO RETURN TEXT BEFORE THE 1ST SPACE

[JuicyMusic]

Hi, I've tried to adjust this formula with no success.

There will always be a space, then a dash, then another space directly after the job number (9729BN)
I need the formula in column L to return only the job number - not the job number AND the space directly after it.

Here is my text string: 9729BN - Pacific Auto Recycling Center

Here is my formula:

=IF(OR(C2,"*DUMMY*",C2=""),"",
LEFT(C2,FIND(" ",C2)-1))

Thanks you so much

 

[Fluff]

In what way doesn't it work?

 

[JuicyMusic]

If there is not a space after the job number - the formula returns a #Value.
I actually have to add a space after the last letter in the job number.

 

[Fluff]

But you said

There will always be a space, then a dash

so I don't quite understand.

 

[Peter_SSs]

=IF(OR(C2,"*DUMMY*",C2=""),"",
LEFT(C2,FIND(" ",C2)-1))

Also, can you explain in words what the red part of the formula is trying to do?

 

[JuicyMusic]

Hello, thank you for responding.

I received a monthly export from our system.
The number of rows varies but could be in the hundreds.
Occasionally there are cells in Column B that are blank (out of 500 rows there could be only 3 cells that are blank)
I will have to manually enter the job number (example: 9729BN or 8123A) (the letters at the end are the Managers initials and could be 1 or more letters)

DUMMY: When the IT department tests something - there will be the word DUMMY - and the formula will return a #Value which is just fine.

The issue is that when I manually enter a job number in the blank cell in Column B - the formula in col L requires me to add a space so that it returns the job number.

The yellow highlighted row is what i manually entered to show you the result in column L. Thank you!!!

 

[Fluff]

Ok, how about

=LEFT(C2,FIND(" ",C2&" ")-1)

then you can filter out the cells that return "Dummy"

 

[JuicyMusic]

SOLVED! Hmmmpfff.....You are amazing. So simple and clean.

 

[Fluff]

Glad we could help & thanks for the feedback.