How to extract word upto specified word by single formula?

[London12F]

How to extract words upto specified word ("Co ", "Eng", "Construction", "(") by single formula (not by VBA)(not by array formula)?

i.e.

ABC DE Co Ltd

FGH IJ KL Eng Co Ltd

ABC Construction Ltd

ABC (China) Co Ltd

to

ABC DE

FGH IJ KL

ABC

ABC

<colgroup><col></colgroup><tbody>
</tbody>

 

[Rick Rothstein]

How to extract words upto specified word ("Co ", "Eng", "Construction", "(") by single formula (not by VBA)(not by array formula)?

That is a lot of restrictions for a text string manipulations problem. Is there a maximum length to the text that can be in a cell?

 

[London12F]

Hi Rick Rothstein,

There is no maximum length to the text, but it should be upto 3 words.

 

[Rick Rothstein]

There is no maximum length to the text, but it should be upto 3 words.

Then I am pretty sure there is no way to do this with a normally entered formula... and I am reasonably sure that any array-entered formula is going to be "ugly" at best. While you don't want a VBA solution, I am going to post one for future readers of this thread (you will have to wait for someone better versed in formulas than I am to try and give you what you have asked for). The following function can be called from other VBA code or used as a UDF (user defined function) within an Excel formula...

Function UpperCaseWords(S As String) As String
  Dim X As Long
  For X = 1 To Len(S)
    If Mid(S, X, 1) Like "[a-z]" Then
      UpperCaseWords = Left(S, InStrRev(Left(S, X), " ") - 1)
      Exit Function
    End If
  Next
End Function

The above function pulls out upper case words up to, but not including, the first word with a lower case letter in it.

 

[Aladin Akyurek]

Does this satisfy?

In B2 enter and copy down:

=TRIM(LEFT(A2,LOOKUP(9.99999999999999E+307,SEARCH(" "&{"co*","(*","eng"}&" "," "&A2&" "))-1))

 

[London12F]

Thanks Aladin Akyurek. It's work.

 

[Aladin Akyurek]

Thanks Aladin Akyurek. It's work.

You are welcome.