• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Take the serial number and display to its corresponding alphabet

V

VDS

Member
@ Dear all,

I have some typical query. As per the attached data, there are two columns one is Srl No 1 to 26 and two is alphabets A to Z

If I am typing any alphabet, it should take the corresponding letter from column 1 and display.
For EG. ABACK = 121311, VDS=22419.


VDS
 

Attachments

  • DATA 123.xls
    14 KB · Views: 4
Hi VDS,

Concatenate of array in a single cell.. in formula base approach.. is really a hard task..

Is VBA acceptable.. !!
 
@Debraj,

Any thing is acceptable. In fact, it is not from the current job and I want to learn the concept. Thats all.


VDS
 
Hi, VDS!

I agree with Debraj(ex-Roy), it's not recommended... unless you:
a) know the maximum length of column D values (let us say 10)
b) have a bunch of helper columns (10) (from columns G:p)

Then you can do this:

E16: =G16&H16&I16&J16&K16&L16&M16&N16&O16&P16

G16: =SI.ERROR(BUSCARV(EXTRAE($D16;COLUMNA()-6;1);$A:$B;2;FALSO);"") -----> in english: =IFERROR(VLOOKUP(MID($D16,COLUMN()-6,1),$A:$B,2,FALSE),"")
and copy across thru P16

Copy E16:p16 up and down as required.

Regards!

PS: Please replace the dumb :p by ": P" unquoted and without space.

PS2: @r1c1, once you asked what other default smiles could be replaced besides :D and if I don't remember wrong I sent you a list where : P was included, didn't I?

EDITED (SirJB7)

PS3: @r1c1, no, I didn't include : P but I think it'd be a good idea. And in my case I'd like these 2:
http://chandoo.org/forum/threads/emoticons.13578/#post-80673
Just for OFFSET use :)
 
Last edited:
@Sir JB7,

Seems difficult to digest. Can u simplify this. Here, maximum of 15 characters is preferred.

VDS
 
In case of VBA..

Try this UDF..

Code: 
Function LookConcat(what As String, where As Range, whichColumn As Integer)
    Dim words() As Byte
    words = StrConv(StrConv(what, vbUpperCase), vbFromUnicode)
    For I = 0 To UBound(words)
        LookConcat = LookConcat & _
            Application.VLookup(ChrW(words(I)), where, whichColumn, False)

'       Just in case if A is always 1 :)
'       LookConcat = LookConcat & words(I) - 64

    Next I
End Function

Use like below..
=LookConcat("car",A2:B27,2)
 
Hi, VDS!
Go with @Debraj(ex-Roy)'s VBA solution, it's the classic and recommended option, my approach was just to show how cumbersome is doing it by formulas.
Regards!
 
Hi, VDS!
Do you think you can manage to handle by yourself the modifications for 2003 version? I hope you can, otherwise you'll be using/distributing/supporting a formula for which you don't even know how to embed it within a simple IF instruction.
Almost 200 posts, I believe that at least you should be able to do that.
Regards!
 
@Sir JB7,

I will do my best and improve it. Still I am a learner in excel. Student learns perfect only when he is forced to do so by his class teacher.

VDS
 
@Debraj,

The function works like a magic. in the following code,

=LookConcat("car",A2:B27,2). Instead of "Car", cell reference can be done. A2: B7 is the range. What does 2 represents ?

VDS
 
You must log in or register to reply here.
Back
Top