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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|