vlookup to find match only part of a text value
 

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!

david

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 = _

"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklm nopqrstuvwxyz"
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]

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 = _

"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklm nopqrstuvwxyz"
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]

vlookup to find match only part of a text value
 

Hi David,

you might like to have a look through this current thread:

http://groups.google.com/group/micro...c63e9d55868253

This is also concerned with partial lookups, so you might be able to
get some tips from it, although it is related to numbers rather than
text strings.

Hope this helps.

Pete

On Aug 28, 6:40*pm, David wrote:
Thank you for your response but VB is now a bit beyond my current comfort
level. Do you have any other suggestions?

thanks!

vlookup to find match only part of a text value
 

Are you still monitoring this thread, David? I have a solution for
your partial matching of up to 15 characters.

Pete

On Aug 28, 9:03*pm, Pete_UK wrote:
Hi David,

you might like to have a look through this current thread:

http://groups.google.com/group/micro...isc/browse_frm...

This is also concerned with partial lookups, so you might be able to
get some tips from it, although it is related to numbers rather than
text strings.

Hope this helps.

Pete

On Aug 28, 6:40*pm, David wrote:



Thank you for your response but VB is now a bit beyond my current comfort
level. Do you have any other suggestions?

thanks!- Hide quoted text -

- Show quoted text -