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!