I have an ugly vendor database (around 50,000 rows) from different sources
and the names of the vendors look like this example:

ABC Company
A B C Company
A.B.C. Company
ABC Co.
Meridian Inc
Meridian Incorporation

Im thinking in a formula that extracts all but the spaces and special
characters, in that way my database will look like this:

ABCCompany
ABCCompany
ABCCompany
ABCCo
MeridianInc
MeridianIncorporation


Then I can put a formula like =Left(A1,5) and that will give me common names
as:
ABCCo
ABCCo
ABCCo
ABCCo
Merid
Merid

My two questions a
1. How do I extract all but spaces and special characters?
2. I really want to extract around 7 characters and dont receive an error
if the numbers of characters are less than 7. How can I do that?

Thanks for your help!!

Hi,

A UDF will do it. Alt+F11 to open VB editor. Right click 'This Workbook' and
insert module and paste this in.

Function GetAlpha(target As Range)
Dim MyStr As String, i As Integer
MyStr = ""
If Len(target.Value) = 0 Then GoTo GoExit
For i = 1 To Len(target.Value)
If UCase(Mid(target, i, 1)) = Chr(65) And _
UCase(Mid(target, i, 1)) <= Chr(90) _
Then MyStr = MyStr & Mid(target, i, 1)
Next i
GoExit:
GetAlpha = Left(MyStr, 7)
End Function

call with
=GetAlpha(a1)

Mike

"Alfredo_CPA" wrote:

I have an ugly vendor database (around 50,000 rows) from different sources
and the names of the vendors look like this example:

ABC Company
A B C Company
A.B.C. Company
ABC Co.
Meridian Inc
Meridian Incorporation

Im thinking in a formula that extracts all but the spaces and special
characters, in that way my database will look like this:

ABCCompany
ABCCompany
ABCCompany
ABCCo
MeridianInc
MeridianIncorporation


Then I can put a formula like =Left(A1,5) and that will give me common names
as:
ABCCo
ABCCo
ABCCo
ABCCo
Merid
Merid

My two questions a
1. How do I extract all but spaces and special characters?
2. I really want to extract around 7 characters and dont receive an error
if the numbers of characters are less than 7. How can I do that?

Thanks for your help!!

Thanks Mike. That does the job perfectly!!! Yo made my day!!
I really appreciate your help!!
--
I'm not a looser, I keep trying€¦


"Mike H" wrote:

Hi,

A UDF will do it. Alt+F11 to open VB editor. Right click 'This Workbook' and
insert module and paste this in.

Function GetAlpha(target As Range)
Dim MyStr As String, i As Integer
MyStr = ""
If Len(target.Value) = 0 Then GoTo GoExit
For i = 1 To Len(target.Value)
If UCase(Mid(target, i, 1)) = Chr(65) And _
UCase(Mid(target, i, 1)) <= Chr(90) _
Then MyStr = MyStr & Mid(target, i, 1)
Next i
GoExit:
GetAlpha = Left(MyStr, 7)
End Function

call with
=GetAlpha(a1)

Mike

"Alfredo_CPA" wrote:

I have an ugly vendor database (around 50,000 rows) from different sources
and the names of the vendors look like this example:

ABC Company
A B C Company
A.B.C. Company
ABC Co.
Meridian Inc
Meridian Incorporation

Im thinking in a formula that extracts all but the spaces and special
characters, in that way my database will look like this:

ABCCompany
ABCCompany
ABCCompany
ABCCo
MeridianInc
MeridianIncorporation


Then I can put a formula like =Left(A1,5) and that will give me common names
as:
ABCCo
ABCCo
ABCCo
ABCCo
Merid
Merid

My two questions a
1. How do I extract all but spaces and special characters?
2. I really want to extract around 7 characters and dont receive an error
if the numbers of characters are less than 7. How can I do that?

Thanks for your help!!

Glad I could help

"Alfredo_CPA" wrote:

Thanks Mike. That does the job perfectly!!! Yo made my day!!
I really appreciate your help!!
--
I'm not a looser, I keep trying€¦


"Mike H" wrote:

Hi,

A UDF will do it. Alt+F11 to open VB editor. Right click 'This Workbook' and
insert module and paste this in.

Function GetAlpha(target As Range)
Dim MyStr As String, i As Integer
MyStr = ""
If Len(target.Value) = 0 Then GoTo GoExit
For i = 1 To Len(target.Value)
If UCase(Mid(target, i, 1)) = Chr(65) And _
UCase(Mid(target, i, 1)) <= Chr(90) _
Then MyStr = MyStr & Mid(target, i, 1)
Next i
GoExit:
GetAlpha = Left(MyStr, 7)
End Function

call with
=GetAlpha(a1)

Mike

"Alfredo_CPA" wrote:

I have an ugly vendor database (around 50,000 rows) from different sources
and the names of the vendors look like this example:

ABC Company
A B C Company
A.B.C. Company
ABC Co.
Meridian Inc
Meridian Incorporation

Im thinking in a formula that extracts all but the spaces and special
characters, in that way my database will look like this:

ABCCompany
ABCCompany
ABCCompany
ABCCo
MeridianInc
MeridianIncorporation


Then I can put a formula like =Left(A1,5) and that will give me common names
as:
ABCCo
ABCCo
ABCCo
ABCCo
Merid
Merid

My two questions a
1. How do I extract all but spaces and special characters?
2. I really want to extract around 7 characters and dont receive an error
if the numbers of characters are less than 7. How can I do that?

Thanks for your help!!

Alfredo

How about correcting the spelling in your sig?

The proper spelling is "loser"

"looser" means not as tight


Gord Dibben MS Excel MVP

On Thu, 18 Sep 2008 12:50:01 -0700, Alfredo_CPA
.(donotspam) wrote:

Thanks Mike. That does the job perfectly!!! Yo made my day!!
I really appreciate your help!!
--
I'm not a looser, I keep trying…