How to extract word upto specified word by single formula?

[London12F]

[TABLE="width: 502"]
<colgroup><col></colgroup><tbody>[TR]
[TD]How to extract words upto specified word ("Co ", "Eng", "Construction", "(") by single formula (not by VBA)(not by array formula)?[/TD]
[/TR]
[TR]
[TD]　[/TD]
[/TR]
[TR]
[TD]i.e.[/TD]
[/TR]
[TR]
[TD]　[/TD]
[/TR]
[TR]
[TD]ABC DE Co Ltd[/TD]
[/TR]
[TR]
[TD]FGH IJ KL Eng Co Ltd[/TD]
[/TR]
[TR]
[TD]ABC Construction Ltd[/TD]
[/TR]
[TR]
[TD]ABC (China) Co Ltd[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]to[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]ABC DE[/TD]
[/TR]
[TR]
[TD]FGH IJ KL[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[/TR]
</tbody>[/TABLE]

 

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