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

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]