Vlookup every single character of a cell's text

[Jubinell]

Hi,

I have a series of cells that contain some texts of varying lengths.

I'd like to vlookup each and every single character of a particular text. If the result is found, great. If the result is null, I want to return a particular character. If the looked up character cannot be found, I want to return another character. In the end, I want to combine all of these results into a single text string.

So for example, if my lookup table is like this:
A apple
B bean
C computer
D

And say the null qualifier is *null* and the not found qualifier is *NA*, here are some expected results of my formula F():

F("ABC") = "applebeancomputer"
F("BD") = "bean*null*"
F("CDE") = "computer*null**NA*"
F("") = ""

Any ideas to do this with a nested formula?
If not, UDFs would also be fine (although less desirable).

 

[Sandeep Warrier]

Hi

I'm not sure that WS Functions would help since CONCATENATE cannot be used with Array Functions (and I think you would need Array WS Functions).. I may be wrong though....

However, try UDF

Function f(vInput As Variant, Optional LookupRange As Range) As String
    Dim WFN As WorksheetFunction
    Dim i As Long
    Set WFN = Application.WorksheetFunction
    f = ""
    For i = 1 To Len(vInput)
        If WFN.CountIf(LookupRange.Columns(1), Mid(vInput, i, 1)) > 0 Then
            If Not IsEmpty(WFN.VLookup(Mid(vInput, i, 1), LookupRange, 2, 0)) Then
                f = f & WFN.VLookup(Mid(vInput, i, 1), LookupRange, 2, 0)
            Else
                f = f & "*null*"
            End If
        Else
            f = f & "*N/A*"
        End If
    Next i
End Function

Excel Workbook
	A	B	C	D	E
19	A	apple			applebeancomputer
20	B	bean			bean*null*
21	C	computer			bean*N/A*
22	D
Core Patch

Excel 2003

Cell Formulas
Range	Formula
E19	=F("ABC",$A$19:$B$22)
E20	=F("BD",$A$19:$B$22)
E21	=F("BE",$A$19:$B$22)
E22	=F("",$A$19:$B$22)

<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden"><input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[Jubinell]

Sorry for the late reply but I did eventually get to run this successfully. Thank you!

 

[Sandeep Warrier]

Sorry for the late reply but I did eventually get to run this successfully. Thank you!

You're welcome!