Formula does not return the correct in all conditions

[AB33]

Column A Column B (return)
WindowsDevice 5.1.478 Windows
Symbole 5.2.0 Symbole
Symbole Symbole
BloomBerry 7.0.0 Bloom
Banan 4.0.1 Banan


=LEFT(A2,FIND(" ",A2))

=LEFT(A2,FIND(" ",A2))&MID(A2,FIND(" ",A2)+1,0)

I have tried both formulas, but do not work in all cells, specially on this line Symbole Symbole

Could some one came up with a formula please?

[dredwolf]

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

[AB33]

Please see attached

[dredwolf]

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:

Please Login or Register  to view this content.

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

Hope this helps

[AB33]

Dred! Thanks for your help!
I think That was what I thought. I think you have the same formula as mine

[dredwolf]

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

[AB33]

Please take your time and try.
Basically for now I wish to return the first word only. For e.g WindowsDevice
should return only "Windows"

[dredwolf]

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

[martindwilson]

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

[dredwolf]

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

[martindwilson]

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

[vlady]

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

[dredwolf]

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

Please Login or Register  to view this content.

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

[AB33]

Dred and Vlady!
Thank you very much for your help!

I do not know how these functions are supposed to work.
My understanding is, it should return the value of source data

When I type the function name, it returns a no value. I then tried to enter the function name, followed by comma, and the then start of the integer. If I type any integer value(1-9), it returns the value of the integer. I am lost. My understanding is that this function has two arguments:
source As String, Start As Integer.
The source is my data in column A, but not sure where the source is coming from.
Thanks for your help

[:) Sixthsense :)]

Finally I ended with the below formula

In B2 cell

Please Login or Register  to view this content.

In C2 cell

Please Login or Register  to view this content.

Refer the attached file for details!

[dredwolf]

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

[AB33]

Thanks every body for your contribution!