Convert Letters to Numbers 4

[Brianfree]

Hi, i have a field which has a value like...

AAFFA


I want to convert letter to a price...

A = 0
B = 1
C = 2
etc..

And have the following positions in the code...

Th H T U D

Which is Thousands, Hundreds, Tens, Units and Decimals

So if i have the following data..

AABCA
ACBCB


The price will be £12.0 & £213.50

Please can anyone help!!!

Kindest regards,

Brian

 

[xlbo]

Always 5 letters or can it vary?



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Brianfree]

Always 5 letters.. Really stuck!

Many thanks,

Brian

 

[MakeItSo]

Hi Brian,

we do not yet know whether you are working in Excel or Word. However, this little bit of VBA code should do just fine:

Dim i As Integer, Price As Double, Prix As String
Dim inp As String [green]'=AAFFA[/green]
Dim outp As String

inp = [green]'whatever contains your text[/green]

For i = 1 To 5
    Prix = Prix & CStr((Asc(Mid(inp, i, 1)) - 65))
Next i
[green]'you have now converted AAFFA to A String 00550[/green]

Price = CDbl(Left(Prix, 4)) + (CDbl(Right(Prix, 1)) / 10)
[green]'Your price as number[/green]
outp = "£" & Format(Price, "##,##0.00")
[green]Your price as currency formatted string[/green]

The code makes use of ASC("A") = 65. So, substracting 65 from the ASCII value of the respective character gives 0 for A, 1 for B, etc.

;-)

Cheers,
MiS

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[combo]

Assuming the code is in A1:
=VALUE((CODE(MID(A1,1,1))-65)&(CODE(MID(A1,2,1))-65)&(CODE(MID(A1,3,1))-65)&(CODE(MID(A1,4,1))-65)&(CODE(MID(A1,5,1))-65))/10
The rest can be get with formatting.

combo

 

[Brianfree]

Sorry, im using Excel 2007.

How do i use this VBA code with this?

Many thanks,

Alan

 

[MakeItSo]

In that case: Don't. Use combo's formula.


[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[Stella740pl]

I would suggest a variation of combo's formula, without converting numbers to text and back. Result of CODE() function is already numeric. So, assuming your coded value is in A1, this formula would translate it into the price.

=(CODE(MID(A1,1,1))-65)*1000+(CODE(MID(A1,2,1))-65)*100+(CODE(MID(A1,3,1))-65)*10+(CODE(MID(A1,4,1))-65)*1+(CODE(MID(A1,5,1))-65)*0.1

Note: your code ACBCB is translated into £212.10, not £213.50.

 

[combo]

... or for code in A2, an array formula, any length string:
=SUM(VALUE(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))-65)*10^(LEN(A2)-ROW(INDIRECT("1:"&LEN(A2)))-1))
(based on David Hager formula in Excel Experts E-Letters)

combo

 

[xlhelp]

Simplifying the "CODE" formula a little further:
[blue]
=((CODE(MID(A1,1,1))-65)&(CODE(MID(A1,2,1))-65)&(CODE(MID(A1,3,1))-65)&(CODE(MID(A1,4,1))-65)&(CODE(MID(A1,5,1))-65))/10[/blue]

I don't particularly care about apathy

 

[byundt]

Brian,
Here is a somewhat simpler formula that uses array constants, but does not need to be array entered.
=SUM((CODE(MID(A1,{5,4,3,2,1},1))-65)*10^{-1,0,1,2,3})

Brad

 

[SkipVought]

Brianfree,

I noticed that you have, since becoming a menber on Feb 6, 2008, started 49 threads and posted 52 replies. Yet you have responded only 4 times to
[blue]
Thank Tek-Tip Member
for this valuable post!
[/blue]
I checked out several replies to your threads and found many valuable posts!

These little [purple]purple stars[/purple] serve several purposes (I'm sure that you have noticed them in this site). First, this small token acts as a "thank you" to the member who posted it. Second, [purple]purple stars[/purple] identify responses within a thread as noteworthy, so that other members can zero in on threads that helped someone. And third, they reflect on the member who issued the [purple]purple star[/purple], as someone who is grateful for valuable information from other members. Not to do so appropriately, is also a reflection on that member.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Stella740pl]

I love byundt's formula.
A star from me. I will archive this for a future reference.

 

[xlhelp]

Brad,

Another object from outer space for you.

I don't particularly care about apathy

 

[SkipVought]

Nice! ==> [purple]*[/purple]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[byundt]

Thanks for all the stars, guys--but the formula I posted is really a rearrangement of the one in David Hager's newsletter that combo cited. Although I developed it completely independently (and took advantage of the "always 5 letters" limitation), combo deserves some credit too ==> *

Brad

 

[Stella740pl]

...but the formula I posted is really a rearrangement of the one in David Hager's newsletter that combo cited.

Well, the link says "Thank byundt for this valuable post!", and it is valuable indeed. It says nothing of being the original inventor - but you do credit the author. As for being a rearrangement, combo mentions it, too, but combo's and yours are two completely different animals, and still both are being rearrangement. I like yours for brevity and ingenuity in using arrays.

But now I am interested in what was David Hager's original formula and how close is it to the final result? While I found David Hager's newsletters, I coudn't guess which one you are referrring to.

 

[fumei]

I notice that Brianfree has not made any comment....

Although it is interesting that one post has:

Many thanks,

Brian



and the other has:

Many thanks,

Alan

Gerry

 

[GSCaupling]

I know I'm missing something obvious since no one mentioned this, but how did the OP get [red]12.0[/red] from [red]AABCA[/red] and [red]213.50[/red] from [red]ACBCB[/red] in his example?
[tt]
AABCA
00120

ACBCB
02121[/tt]

Thanks
GS

[red]******^*******
[small]I[/small] [small]Love[/small] [♥] [small]Redundancy.[/small][/red]

 

[SkipVought]

I know I'm missing something obvious since no one mentioned this, but how did the OP get 12.0 from AABCA and 213.50 from ACBCB in his example?

Maybe the OP's moniker has a dyslexic typo, somewhere in the first 4 characters? ;-)




Skip,

_{Just traded in my old subtlety...
for a NUANCE!}