vlookup to find match only part of a text value

D

David

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
 
D

David

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

thanks!

smartin said:
David said:
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]
 
P

Pete_UK

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

Pete
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top