Convert numbers to text

[moulin.rouge]

Hello,

I have a 17 alphanumeric number that I need to convert into text and add a space in between.

For example:

ABC1289056DWERT90

It should look like:

A B C ONE TWO EIGHT NINE CERO FIVE SIX D W E R T NINE CERO

Is there a function that can help me automate this?

Thank you

 

[RuralGuy]

I'm afraid you will need to roll your own on this one.

 

[raskew]

Hi -

That's a good'un. Try copying/pasting this to a standard module,
then call as shown in the example.

Public Function NumToText6(pstr As String) As String
'Re:       http://www.access-programmers.co.uk/forums/showthread.php?t=169166
'Purpose:  Converts a string to alpha characters and
'          numeric characters expressed as "ONE, "TWO", etc.
'Coded by: raskew
'Input:    ? NumToText6("ABC1289056DWERT90")
'Output:   "A B C ONE TWO EIGHT NINE ZERO FIVE SIX D W E R T NINE ZERO"

Dim strHold  As String
Dim strSay   As String
Dim x        As String
Dim itemHold As String
Dim n        As Integer

   x = Chr(32)  'space
   strHold = Trim(pstr)
   For n = 1 To Len(strHold)
      itemHold = Mid(strHold, n, 1)
      strSay = strSay & IIf(IsNumeric(itemHold), Choose(Val(itemHold) + 1, "ZERO", "ONE", "TWO", "THREE", "FOUR", "FIVE", "SIX", "SEVEN", "EIGHT", "NINE"), itemHold) & x
   Next n

   NumToText6 = Trim(strSay)

End Function

HTH - Bob

 

[RuralGuy]

Didn't you just know Bob was going to take the bait?

 

[raskew]

Hey -

That was a fun problem! Got lucky on that one and was able to write
it start to finish without a correction.

Love it when that happens - Bob

 

[RuralGuy]

Hey - That was a fun problem!

I know. I almost sharpened the pencil for it but was working on several issues on other sites. It did look like it would be fun though. I couldn't help but rib you a little. Hope you don't mind.

 

[raskew]

There's nothing to mind. It was a good break. I'm still fighting this one:
http://www.access-programmers.co.uk/forums/showthread.php?t=169069

Bob

 

[RuralGuy]

There's nothing to mind. It was a good break. I'm still fighting this one:
http://www.access-programmers.co.uk/forums/showthread.php?t=169069

Bob

I think it is your English that is getting in the way.

 

[raskew]

Dear Mr. Rural Guy,

I have this string:

x = "ABC1289056DWERT90"

I need to convert it so it looks like this:

y = "A B C ONE MILLION, TWO-HUNDRED AND EIGHTY-NINE THOUSAND, AND FIFTY-SIX, D W E R T NINETY"

Please help me, my assignment is due at 09:00 AM in the morning.

Prompt reply appreciated. Sorry if my English not too good.

Bob

 

[RuralGuy]

I do not consider your poor scheduling an imperitive on my part. I certainly wish you well Sir.

 

[raskew]

Damn -- isn't this forum where I'm supposed to come if I'm two occupied to do my studies? You sertainly haven't been much help!

Damn!

 

[RuralGuy]

I'm afraid that is the next Forum down the street. It is just two doors down so it will not take too long to get there and they serve alcohol, which might come in handy in your case.

 

[raskew]

...and they serve alcohol, which might come in handy in your case.

Thank you, and if that's how you wanna be, I'll take a case and we'll just forget about the assignment stuff.

Besh wisses - Bobo

 

[RuralGuy]

Huggs and Kisses.

 

[raskew]

Yup! Same here. Just noticed, when I enlarged your picture and saw a partial profile, that you're old like me (although perhaps a tad uglier). Turn 66 tomorrow, 1 April. Having said that, gotta go, still fighting to put my taxes together.

Take care - Bob

 

[RuralGuy]

Was that ever a Fools Joke on your mom. Not very nice I would say. Haaa, I won't be 65 'til November. Gotcha by a year and a half.

Later Bob.

 

[raskew]

And here I thought I was leaving. However,

Was that ever a Fools Joke on your mom.

Someone pointed that out to me today. Had always thought about the harassment (spelling/English?) I'd taken over the years re my birthday, but had never, ever, thought that my mom might have faced the same thing.

Do you have an info on early Alzheimer's disease?

Bob

 

[RuralGuy]

Yes...but I forgot where I put it.

 

[moulin.rouge]

Thank you very much Bob! this works perfect!

 

[RuralGuy]

You will come to expect nothing less from Bob.