Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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] |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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] |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search/Match/Find ANY part of string to ANY part of Cell Value | Excel Worksheet Functions | |||
How do I use VLOOKUP to find part of string? | Excel Worksheet Functions | |||
find text in cell and replace it with part of the text in that ce. | Excel Discussion (Misc queries) | |||
Find and replace part of a text string | Excel Discussion (Misc queries) | |||
If Match Part of Text Within Cell, Then Blank | Excel Worksheet Functions |