Convert 1 Character Code to a word

[Pookie76]

I would like to do as the subject states.
For example, If something was ACA where A represented Alpha and
represented Cyber, the sequence would read Alpha-Cyber-Alpha. Is thi
possible? Thanks in advance

 

[Bernard Liengme]

This will do for a short list
=LOOKUP(MID(A4,1,1),{"A","B","C"},{"Alpha","Beta","Cyber"}&"-"&LOOKUP(MID(A4
,2,1),{"A","B","C"},{"Alpha","Beta","Cyber"}&"-"&LOOKUP(MID(A4,3,1),{"A","B"
,"C"},{"Alpha","Beta","Cyber"}))

But for anything longer you would need a table for the lookup.

 

[CLR]

Maybe something like............

=VLOOKUP(LEFT(A4,1),mytable,2,FALSE)&"-"&VLOOKUP(MID(A4,2,1),mytable,2,FALSE
)&"-"&VLOOKUP(RIGHT(A4,1),mytable,2,FALSE)

Just need to set up MyTable to contain all the desired letter-codes and only
use 3-character inputs, unless you change it to accomodate more or
less..........


Vaya con Dios,
Chuck, CABGx3

 

[BenjieLop]

The simplest way is probably to set up a lookup table that looks lik
this:

A Alpha
B Bravo
C Cyber
D Delta
E Epsilon

and in your chosen cell, enter the ff

=vlookup(left(C1,1),Table,2,0)&"-"&vlookup(mid(C1,2,1),Table,2,0)&"-"&vlookup(right(C1,1)Table,2,0)

where C1 is assumed to be the cell that contains "ACA" and "Table" i
the name of your lookup table.

Since the range of the lookup table is A1:B5, you can replace "Table
in the formula with "A1:B5" and you can also replace the "0" -- th
last argument in the vlookup formula -- with "false."

Hope this helps

 

[Pookie76]

Wow, thanks for the help guys. I'll try it but I think it'll take me
while to understand what you guys wrote. <---Noo

 

[Pookie76]

This is the code that I used that works for 3 variables:
=VLOOKUP(LEFT(C1,1),AB,2,0)&"-"&VLOOKUP(MID(C1,2,1),AB,2,0)&"-"&VLOOKUP(RIGHT(C1,1),AB,2,0)

If I want to post an unlimited amount of 1 letter coding instead o
only 3, how do I change it to make my current code acceptable? Thank
in advance

 

[Frank Kabel]

Hi
if you have an 'unlimited number of characters you'll have to use VBA
(IMHO)

 

[Pookie76]

What is VBA? Thanks.

Hi
if you have an 'unlimited number of characters you'll have to us
VBA
(IMHO)

 

[Frank Kabel]

Hi
VBA is the macro/programming language of MS Office applications (see:
http://www.mvps.org/dmcritchie/excel/getstarted.htm)

--
Regards
Frank Kabel
Frankfurt, Germany

What is VBA? Thanks.

Hi
if you have an 'unlimited number of characters you'll have to use
VBA
(IMHO)

 

[Amedee Van Gasse]

What is VBA? Thanks.

Ooohhh...
If you ask that question, you're not ready for it yet.

You have 3 possibilities:
1. Start learning NOW. Go to your local library and read some books
about Excel and VBA. Don't buy books if you don't know yet if VBA is
the thing for you. But if you like the books from your library, you can
always buy them later.
2. Get someone to do it for you. And be prepared to pay - Excel
consultants are usually not very cheap. Or be very nice in this
newsgroup - perhaps someone will do it for free in his/her spare time.
3. Use a search engine to find out if it has already been programmed by
someone else.

--
Amedee Van Gasse using XanaNews 1.16.3.1
If it has an "X" in the name, it must be Linux?
Please don't thank me in advance. Thank me afterwards if it works or
hit me in the face if it doesn't. ;-)

I found 2 small shortcomings on www.excelforum.com:
1. You do *not* have to pay for access to 2. A *real* newsreader (not Outlook Express) is actually very easy to
use. See www.newsreaders.com for more info.

 

[Pookie76]

Or be very nice in this
newsgroup - perhaps someone will do it for free in his/her spar
time.
3. Use a search engine to find out if it has already been programme
by
someone else.
[/B]

How about if it wasn't unlimited but had a maximum cap at aroun
50? Would I still be able to use the code:
=VLOOKUP(LEFT(C1,1),AB,2,0)&"-"&VLOOKUP(MID(C1,2,1),AB,2,0)&"-"&VLOOKUP(RIGHT(C1,1),AB,2,0)

I noticed that it is only limited to 3 because of the Left, Mid an
Right. Is that true? Is there any way I could list it manually so th
limit isn't capped at three?

Btw Amedee, i'll thank in advance because any help would be beneficia
even if it doesn't work. ;

 

[Dave Peterson]

Keep your lookup table on another sheet (sheet2???) in columns A:B,

Then you could use a userdefined function like this:

Option Explicit
Function MyConversion(rng As Range) As String

Dim res As Variant
Dim LookUpTable As Range
Dim iCtr As Long
Dim myStr As String

Set rng = rng(1)
Set LookUpTable = Worksheets("sheet2").Range("a:b")

myStr = ""
For iCtr = 1 To Len(rng.Value)
res = Application.VLookup(Mid(rng.Value, iCtr, 1), _
LookUpTable, 2, False)
If IsError(res) Then
myStr = myStr & "-?"
Else
myStr = myStr & "-" & res
End If
Next iCtr

If myStr <> "" Then
myStr = Mid(myStr, 2)
End If

MyConversion = myStr

End Function

Since you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

=======

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Type =myconversion(A1)

If you give it an abbreviation not included in your table, it'll show a question
mark.

Or be very nice in this
newsgroup - perhaps someone will do it for free in his/her spare
time.
3. Use a search engine to find out if it has already been programmed
by
someone else.
[/B]

How about if it wasn't unlimited but had a maximum cap at around
50? Would I still be able to use the code:
=VLOOKUP(LEFT(C1,1),AB,2,0)&"-"&VLOOKUP(MID(C1,2,1),AB,2,0)&"-"&VLOOKUP(RIGHT(C1,1),AB,2,0)

I noticed that it is only limited to 3 because of the Left, Mid and
Right. Is that true? Is there any way I could list it manually so the
limit isn't capped at three?

Btw Amedee, i'll thank in advance because any help would be beneficial
even if it doesn't work.

 

[Pookie76]

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Type =myconversion(A1)
[/email] [/B]

I copied the code into module1, transferred my table to sheet 2 an
inserted myconversion(A1) into cell A1. What do I do from here? Do
need to write another code in excel for my sequence to convert? Thank
again! :

 

[Dave Peterson]

Put your code to translate into A1 (ABC).

Then put =myConversion(a1) in B1

and see what shows up in B1!

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Type =myconversion(A1)
[/email] [/B]

I copied the code into module1, transferred my table to sheet 2 and
inserted myconversion(A1) into cell A1. What do I do from here? Do I
need to write another code in excel for my sequence to convert? Thanks
again!

 

[Pookie76]

*Put your code to translate into A1 (ABC).

Then put =myConversion(a1) in B1

and see what shows up in B1!

[/email] *

I got #NAME?

I copied your code into the macro.

My table in sheet 2 is from A1 to B20.
The option explicit is in the declaration and ever other part of th
macro is in my conversion.

:confused

 

[Dave Peterson]

Name usually means that there's a spelling mismatch between what's in the cell
(=myConversion(a1)) and what the macro is named in the VBE.

Function MyConversion(rng As Range) As String

If those two things match, my next guess is that you didn't put it in a General
module--you may have put it behind a worksheet or behind ThisWorkbook.

If you did that, move it to a general module.

*Put your code to translate into A1 (ABC).

Then put =myConversion(a1) in B1

and see what shows up in B1!

[/email] *

I got #NAME?

I copied your code into the macro.

My table in sheet 2 is from A1 to B20.
The option explicit is in the declaration and ever other part of the
macro is in my conversion.

 

[Pookie76]

*Name usually means that there's a spelling mismatch between what'
in the cell
(=myConversion(a1)) and what the macro is named in the VBE.

Function MyConversion(rng As Range) As String

If those two things match, my next guess is that you didn't put it i
a General
module--you may have put it behind a worksheet or behin
ThisWorkbook.

If you did that, move it to a general module.

*

Thank you!!! IT WORKS!!! Instead of putting it in only the Module,
put it in all 3 sheets too. :eek

 

[Dave Peterson]

Welcome to the world of VBA.

You'll see the sun in a few years!

 

[Pookie76]

*Welcome to the world of VBA.

You'll see the sun in a few years!
*

Now i'm trying tot take the macro that you wrote and recreate an
additional module so that =myConversion(A1) can be converted into a
number by adding up all the variables.

I created a 2nd table on sheet 3 taking the 3 letter code of my 1
letter codes and giving them each a number. Depending on the three
letter code, the second module would add all the numbers up and it
would be 1 whole number.

I was going to do this by converting 3 letter code into a number, then
convert that cell into a sum cell.

I took the module that you wrote and changed three things.

Option Explicit
Function *myConversionA*(rng As Range) As String

Dim res As Variant
Dim LookUpTable As Range
Dim iCtr As Long
Dim myStr As String

Set rng = rng(1)
Set LookUpTable = Worksheets("*sheet3"*).Range("a:b")

myStr = ""
For iCtr = 1 To Len(rng.Value)
res = Application.VLookup(Mid(rng.Value, iCtr, 1), _
LookUpTable, 2, False)
If IsError(res) Then
myStr = myStr & "-?"
Else
myStr = myStr & "-" & res
End If
Next iCtr

If myStr <> "" Then
myStr = Mid(myStr, 2)
End If

*myConversionA* = myStr

End Function

The second table that I created is similar to the first one (1 letter
code is in A and 3 letter code is in B) the second table is (3 letter
code in A and the assigned number to it is in B)

I don't get the NAME error but i do get:

?-?-?-?-?-?-?-?-?-?-?-?-?





and I thought I was on a roll here.

Help...

 

[Debra Dalgleish]

There's a sun?

Welcome to the world of VBA.

You'll see the sun in a few years!