MSGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

### Formula Help - Split capital letters from first part of string

• Follow

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:

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?

 0

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?
>
 0

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

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?
> >
 0

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:
>
>
>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?
>

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
 0

3 Replies
656 Views

8/23/2012 6:50:42 AM