Hello,

I have searched the web but can't seem to find the solution for this 
problem.  I have a string of data in a column of cells that look like this:

COMPANY NAME Contact Person Address
COMPANY NAME Contact Person Address

I need to pull the company name (which are in all caps) from the first part 
of this string for each cell.  How do I get Excel to pull the company name 
(anywhere from 1 words to 6 or 7 words) and leave the rest?

Thanks in advance for your help!

HI,

Try this. ALT+F11 tp open Vb editor. Right click 'This workbook' and insert 
module and paste the code below in.

Call with

=getcaps(a1)

where A1 contains the string. Drag down as required.


Function getcaps(rng As Range) As String
For x = 1 To Len(rng)
    If Mid(rng, x, 1) Like "[A-Z]" And _
    Not Mid(rng, x + 1, 1) Like "[a-z]" Then
        If Mid(rng, x + 1, 1) <> " " Then
        getcaps = getcaps & Mid(rng, x, 1)
        Else
        getcaps = getcaps & Mid(rng, x, 1) & " "
        End If
    End If
Next
End Function
-- 
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that 
introduces the fewest assumptions while still sufficiently answering the 
question.


"Beverly-Texas" wrote:

> Hello,
> 
> I have searched the web but can't seem to find the solution for this 
> problem.  I have a string of data in a column of cells that look like this:
> 
> COMPANY NAME Contact Person Address
> COMPANY NAME Contact Person Address
> 
> I need to pull the company name (which are in all caps) from the first part 
> of this string for each cell.  How do I get Excel to pull the company name 
> (anywhere from 1 words to 6 or 7 words) and leave the rest?
> 
> Thanks in advance for your help!

Beverly,

I should have added that to work the relies on there NOT being any 2 or more 
consecutive capital letters after the company name

COMPANY NAME Contact Person Address

In your example above you show Proper case for the address so the UDF will 
ignore them
-- 
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that 
introduces the fewest assumptions while still sufficiently answering the 
question.


"Mike H" wrote:

> HI,
> 
> Try this. ALT+F11 tp open Vb editor. Right click 'This workbook' and insert 
> module and paste the code below in.
> 
> Call with
> 
> =getcaps(a1)
> 
> where A1 contains the string. Drag down as required.
> 
> 
> Function getcaps(rng As Range) As String
> For x = 1 To Len(rng)
>     If Mid(rng, x, 1) Like "[A-Z]" And _
>     Not Mid(rng, x + 1, 1) Like "[a-z]" Then
>         If Mid(rng, x + 1, 1) <> " " Then
>         getcaps = getcaps & Mid(rng, x, 1)
>         Else
>         getcaps = getcaps & Mid(rng, x, 1) & " "
>         End If
>     End If
> Next
> End Function
> -- 
> Mike
> 
> When competing hypotheses are otherwise equal, adopt the hypothesis that 
> introduces the fewest assumptions while still sufficiently answering the 
> question.
> 
> 
> "Beverly-Texas" wrote:
> 
> > Hello,
> > 
> > I have searched the web but can't seem to find the solution for this 
> > problem.  I have a string of data in a column of cells that look like this:
> > 
> > COMPANY NAME Contact Person Address
> > COMPANY NAME Contact Person Address
> > 
> > I need to pull the company name (which are in all caps) from the first part 
> > of this string for each cell.  How do I get Excel to pull the company name 
> > (anywhere from 1 words to 6 or 7 words) and leave the rest?
> > 
> > Thanks in advance for your help!

On Thu, 4 Feb 2010 12:22:11 -0800, Beverly-Texas
<BeverlyTexas@discussions.microsoft.com> wrote:

>Hello,
>
>I have searched the web but can't seem to find the solution for this 
>problem.  I have a string of data in a column of cells that look like this:
>
>COMPANY NAME Contact Person Address
>COMPANY NAME Contact Person Address
>
>I need to pull the company name (which are in all caps) from the first part 
>of this string for each cell.  How do I get Excel to pull the company name 
>(anywhere from 1 words to 6 or 7 words) and leave the rest?
>
>Thanks in advance for your help!

One way is with a User Defined Function.

The following makes the assumptions that
	Company Name consists ONLY of capital letters and <spaces>
	There is a <space> at the end of the company name, prior to the start
of the other data.

To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like 

	=CompanyName(A1)

 in some cell.

============================================================
Option Explicit
Function CompanyName(s As String) As String
  Dim myRegExp, myMatches

Set myRegExp = CreateObject("vbscript.regexp")
myRegExp.Pattern = "\s*\b([A-Z\s]+)\b(?=\s)"
Set myMatches = myRegExp.Execute(s)
If myMatches.Count >= 1 Then
    CompanyName = myMatches(0).submatches(0)
Else
    CompanyName = ""
End If

End Function
=================================
--ron