Formula Help - Split capital letters from first part of string

[Beverly-Texas]

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!

 

[Mike H]

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.

 

[Mike H]

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.

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.

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!

 

[Ron Rosenfeld]

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