|
|
|
date: Wed, 27 Aug 2008 17:44:00 -0700,
group: microsoft.public.excel.misc
back
Re: vlookup to find match only part of a text value
David wrote:
> am trying to match two columns of data were column b has text values that
> are very similar but not formatted exactly the same as column a. The slight
> formatting differences result null values on the vlookup. One solution for
> this challenge is to match the first 15 characters of a cell rather than the
> entire cell value. Can someone tell me how I can direct vlookup to look at a
> restricted character count? If vlookup isn't the right choose what is?
>
> For what it's worth, here's an example of the format challenge that's
> killing my vlookup. ABC Firm LLC vs. ABC Firm, LLC.
>
> Thanks!
Hi david
If you are up for a little VBA, I have used the code below to strip
source text of punctuation for just such a purpose.
Watch for line warp... lol wrap...
---[CODE BEGIN]
Public Function StripPunctuation(StringIn As String, _
Optional SaveDelimiters As String = "", _
Optional ReplaceChar As String = "" _
) As String
' Purpose: Removes or replaces characters in a string that are not
alphanumeric or other specified characters.
' Useful for stripping punctuation and symbols from text.
' Usage: StripPunctuation(StringIn [,SaveDelimiters] [,ReplaceChar])
' StringIn: Required; The string to be processed.
' SaveDelimiters: Optional; Allow these characters in addition to the
default alphanumerics. No default.
' Note: often, you will want to specify space for this parameter.
Multiple characters can be specified
' here, e.g., StripPunctuation(StringIn, " .,") will preserve
space, period, and comma.
' ReplaceChar: Optional; If ReplaceChar is specified it will be used
to replace disallowed characters.
' if not specified, disallowed characters will be dropped from the
output.
Const AllowChars As String = _
"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz"
Dim tmp As String
Dim L As Long
Dim j As Long
Dim C As String
Dim AllowedString As String
AllowedString = SaveDelimiters & AllowChars
L = Len(StringIn)
j = 1
Do While j <= L
C = Mid(StringIn, j, 1)
If InStr(1, AllowedString, C) > 0 Then
tmp = tmp & C
Else
tmp = tmp & ReplaceChar
End If
j = j + 1
Loop
StripPunctuation = tmp
End Function
---[CODE END]
date: Wed, 27 Aug 2008 21:17:58 -0400
author: smartin
Re: vlookup to find match only part of a text value
Thank you for your response but VB is now a bit beyond my current comfort
level. Do you have any other suggestions?
thanks!
"smartin" wrote:
> David wrote:
> > am trying to match two columns of data were column b has text values that
> > are very similar but not formatted exactly the same as column a. The slight
> > formatting differences result null values on the vlookup. One solution for
> > this challenge is to match the first 15 characters of a cell rather than the
> > entire cell value. Can someone tell me how I can direct vlookup to look at a
> > restricted character count? If vlookup isn't the right choose what is?
> >
> > For what it's worth, here's an example of the format challenge that's
> > killing my vlookup. ABC Firm LLC vs. ABC Firm, LLC.
> >
> > Thanks!
>
> Hi david
>
> If you are up for a little VBA, I have used the code below to strip
> source text of punctuation for just such a purpose.
>
> Watch for line warp... lol wrap...
>
> ---[CODE BEGIN]
> Public Function StripPunctuation(StringIn As String, _
> Optional SaveDelimiters As String = "", _
> Optional ReplaceChar As String = "" _
> ) As String
> ' Purpose: Removes or replaces characters in a string that are not
> alphanumeric or other specified characters.
> ' Useful for stripping punctuation and symbols from text.
> ' Usage: StripPunctuation(StringIn [,SaveDelimiters] [,ReplaceChar])
> ' StringIn: Required; The string to be processed.
> ' SaveDelimiters: Optional; Allow these characters in addition to the
> default alphanumerics. No default.
> ' Note: often, you will want to specify space for this parameter.
> Multiple characters can be specified
> ' here, e.g., StripPunctuation(StringIn, " .,") will preserve
> space, period, and comma.
> ' ReplaceChar: Optional; If ReplaceChar is specified it will be used
> to replace disallowed characters.
> ' if not specified, disallowed characters will be dropped from the
> output.
>
> Const AllowChars As String = _
>
> "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz"
> Dim tmp As String
> Dim L As Long
> Dim j As Long
> Dim C As String
> Dim AllowedString As String
>
> AllowedString = SaveDelimiters & AllowChars
> L = Len(StringIn)
> j = 1
> Do While j <= L
> C = Mid(StringIn, j, 1)
> If InStr(1, AllowedString, C) > 0 Then
> tmp = tmp & C
> Else
> tmp = tmp & ReplaceChar
> End If
> j = j + 1
> Loop
> StripPunctuation = tmp
> End Function
> ---[CODE END]
>
date: Thu, 28 Aug 2008 10:40:04 -0700
author: David
|
|
