A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Extract all text without spaces and special characters



 
 
Thread Tools Display Modes
  #1  
Old September 18th, 2008, 08:13 PM posted to microsoft.public.excel.worksheet.functions
Alfredo_CPA
external usenet poster
 
Posts: 19
Default Extract all text without spaces and special characters

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

I’m 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 don’t receive an error
if the numbers of characters are less than 7. How can I do that?

Thanks for your help!!


  #2  
Old September 18th, 2008, 08:28 PM posted to microsoft.public.excel.worksheet.functions
Mike H
external usenet poster
 
Posts: 8,419
Default Extract all text without spaces and special characters

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

I’m 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 don’t receive an error
if the numbers of characters are less than 7. How can I do that?

Thanks for your help!!


  #3  
Old September 18th, 2008, 08:50 PM posted to microsoft.public.excel.worksheet.functions
Alfredo_CPA
external usenet poster
 
Posts: 19
Default Extract all text without spaces and special characters

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

I’m 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 don’t receive an error
if the numbers of characters are less than 7. How can I do that?

Thanks for your help!!


  #4  
Old September 18th, 2008, 09:02 PM posted to microsoft.public.excel.worksheet.functions
Mike H
external usenet poster
 
Posts: 8,419
Default Extract all text without spaces and special characters

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

I’m 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 don’t receive an error
if the numbers of characters are less than 7. How can I do that?

Thanks for your help!!


  #5  
Old September 18th, 2008, 11:39 PM posted to microsoft.public.excel.worksheet.functions
Gord Dibben
external usenet poster
 
Posts: 20,252
Default Extract all text without spaces and special characters

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…


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:03 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.