Formula does not return the correct in all conditions

could you upload a sample file, showing what your data looks like and what you expect as the result?
keep any personal or confidential information out of your sample, but do have it use representative data (ie - if column A is numbers and formatted as numbers, please include in sample);
it should also have any expected "problem" data (blank cells, mis-entered data, etc..) to make answering the problem easier..

Add a File - click advanced (next to quick post), scroll down until you see "manage attachments", click that and select "add files" (top right corner). click "select files" find your file, select file, click "upload", click 'done" bottom right. click "submit reply"

Hope that helps

okay, part of the problem (at least, from what I see you want returned) is that you are trying to do different types of separation, one can be got by looking for a space, another you actually want to split a word-ie BloomBerry returns Bloom..I don't think you can do all that with just one formula...for MOST of the data you could use:

Formula:

=Iferror(Left(A2,FIND(" ",A2)-1),A2)

in A2, dragged down.
Not sure about that Split though..

Hope this helps

You are welcome
I''m still trying things to separate out Bloom from BloomBerry AND not affect the rest of the solution, but to be honest, I think you would need a completely different filter to do it

Anyways, glad I could help a little bit

Aaaah...Okay, your sample shows you want everything to the first space, or entire string if no space...that does change things a bit..

you can lookup from a list and specify anything you want, see attached

Yes that works.
Although, what if you don't actually know what you are looking for?
I think this is a job for a Macro, or UDF, but it is beyond my abilities as yet..

Working on it, and maybe I'll get lucky and not spend the rest of the night debugging a simple loop...

of course you know what your looking for its easy enough to split with text to columns then extract an unique list. thn you can define anything you want

from NBVC

=LEFT(D2,SMALL(FIND(0,SUBSTITUTE(D2,CHAR(ROW(INDIRECT("65:90"))),0)&0),2)-1) -array


problem.... don't recognize correctly
BloomBerry 0.0.0
BloomBerry 4.5.0

receiving 11th character supposed 6, any thoughts....

Okay, check the attachment and see if that does it
Added a UDF (with many Thanks to Richard Buttrey and jindon,all Kudos and Rep to them please!)

Function FindUpper(source As String, Start As Integer) As Integer
    Dim i As Long
    For i = Start To Len(source)
        If Mid(source, i, 1) = UCase(Mid(source, i, 1)) Then
            FindUpper = i
            Exit Function
        End If
    Next i
    FindUpper = 0
End Function

to add the code, hit <alt>+<F11>,INSERT>Module, copy code to module, then use the Function as needed

{I know this code will turn out very useful in my own project )

Edit
cols F & G were there for testing purpose on my own attempt...

Finally I ended with the below formula

In B2 cell

Formula:

=IF(ISERROR(MATCH(TRUE,EXACT(MID(UPPER(MID($A2,2,FIND(" ",MID($A2,2,255))-1)),ROW(INDIRECT("1:"&LEN(MID($A2,2,FIND(" ",MID($A2,2,255))-1)))),1),MID(MID($A2,2,FIND(" ",MID($A2,2,255))-1),ROW(INDIRECT("1:"&LEN(MID($A2,2,FIND(" ",MID($A2,2,255))-1)))),1)),0)),IF(ISERROR(LEFT($A2,FIND(" ",$A2)-1)),$A2,LEFT($A2,FIND(" ",$A2)-1)),LEFT($A2,MATCH(TRUE,EXACT(MID(UPPER(MID($A2,2,FIND(" ",MID($A2,2,255))-1)),ROW(INDIRECT("1:"&LEN(MID($A2,2,FIND(" ",MID($A2,2,255))-1)))),1),MID(MID($A2,2,FIND(" ",MID($A2,2,255))-1),ROW(INDIRECT("1:"&LEN(MID($A2,2,FIND(" ",MID($A2,2,255))-1)))),1)),0)))

In C2 cell

Formula:

=IFERROR(MID(MID($A2,2,255),MATCH(TRUE,EXACT(MID(UPPER(MID($A2,2,FIND(" ",MID($A2,2,255))-1)),ROW(INDIRECT("1:"&LEN(MID($A2,2,FIND(" ",MID($A2,2,255))-1)))),1),MID(MID($A2,2,FIND(" ",MID($A2,2,255))-1),ROW(INDIRECT("1:"&LEN(MID($A2,2,FIND(" ",MID($A2,2,255))-1)))),1)),0),FIND(" ",MID(A2,2,255))-MATCH(TRUE,EXACT(MID(UPPER(MID($A2,2,FIND(" ",MID($A2,2,255))-1)),ROW(INDIRECT("1:"&LEN(MID($A2,2,FIND(" ",MID($A2,2,255))-1)))),1),MID(MID($A2,2,FIND(" ",MID($A2,2,255))-1),ROW(INDIRECT("1:"&LEN(MID($A2,2,FIND(" ",MID($A2,2,255))-1)))),1)),0)),"")

Refer the attached file for details!

You just use the Formula like any other Excel Formula,ColumnD shows the results
The Parameters are Source( generally a string)and a start position within the source to start looking for AN UPPERCASE letter, it will return the position within the Source that it finds an uppercase letter, or 0 if no uppercase letter is found.

=IFERROR(LEFT(A2,FindUpper(A2,2)-1),A2) will look for an upper case letter in A2, if it finds one, it will return everything before that letter, if it doesn't it will return the entire contents of A2

Hope that helps

-Edit-
I guess i removed the F&G columns, which is good, they were just there for me to test the function