finding a word in a string and adding "and" to the right of it. 1

[Hargreavesk]

Can anyone help?

My database converts an amount to words i.e £513.32 becomes "Five Hundred Thirteen Pounds and 32 pence". Unfortunately, I need to have an "and" between the Hundred and the Thirteen but cannot figure out how to do this.

I have tried the Left and Right functions and "SearchChar" but I'm not getting anywhere.

Any help would be greatly appreciated.

Many thanks

Kate

 

[MichaelRed]

Hmmmmmmmmmmmmmmmmmmmmmmm,

Unless I'm really out of date, the standard (banking) convention is the same here and there and (ALMOST) everwhere. The and "Ain't" supposed tb be where (you say yuu 'need' it). So my first item of advice would be to carefully research the issue and (assuming that I'm correct) present it to the 'requestor'.

Since youth and its' procolivities seldom follow the (first?) advice, you might want to look into the use of "Split" and "Join" or even "Replace", as they should be reasonably simplistic to use for the specific situation.

Following the theme of youthful proclivities, while the above will 'repair' the existing records, it is obvious that the "database" generates the String representation of the numeric values via a procedure, you will ultimately need to visit that procedure and 'correct' the problen at it's source.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[AlexMidd]

How about the Instr function, which looks for occurrences of one string within another? Have fun! )

Alex Middleton

 

[Hargreavesk]

Michael,

Thankyou so much for your advice. Unfortunately, I'm fully researched with APAC's (British) and have been approved by the bank. I wouldn't be killing myself over this if the "and" was not a requirement.

I have tried to correct the problem from the source procedure but it is putting "And" after EVERY hundred. Therefore if I have £500.00, I get "Five Hundred And Pounds". It's driving me nuts...

Sorry

Kate

 

[Hargreavesk]

Alex,

I have used the following in Ctrl+G but something is wrong somewhere:

FullAmount = "One Thousand Four Hundred Fifteen Pounds"
LeftAmount = Left(FullAmount, InStr(FullAmount, " Hundred&quot)
RightAmount = Right(FullAmount, InStr(FullAmount, " Hundred&quot)
Amount = LeftAmount & "and" & RightAmount

The result is this: One Thousand Four andred Fifteen Pounds

Can you see what I'm doing wrong???

Thanks

Kate

 

[AlexMidd]

I think MichealRed assuming we are all American, which we are not. We say "One hundred AND thirteen pounds", not "one hundred thirteen pounds".

No offence (or offense!) to our American cousins (after all I just LOVE baseball and NFL football) but WE invented the language.
Have fun! )

Alex Middleton

 

[AlexMidd]

No, I am not sure. I will test it and see. Instr returns the position of the occurrence of the string in the other string but I'm not sure which position, i.e. the start or the end of the string. Have fun! )

Alex Middleton

 

[AlexMidd]

I see the problem now, Kate. It is with your statement

RightAmount = Right(FullAmount, InStr(FullAmount, " Hundred&quot)

Right allocates to the right-hand number of characters in the main string, whereas you need to allocate to the main string FROM a certain point to the end. You need something like the following:

Dim FullAmount As String, LeftAmount As String
Dim RightAmount As String, Amount As String
Dim Position As Long
FullAmount = "One Thousand Four Hundred Fifteen Pounds"
Position = InStr(FullAmount, " Hundred&quot + Len("Hundred&quot
LeftAmount = Left(FullAmount, Position)
RightAmount = Right(FullAmount, Len(FullAmount) - Position)
Amount = LeftAmount & " and " & RightAmount

The Len("Hundred&quot adds the length of the substring to the Instr function result as it marks the position of the START of the substring, not the end (you could just add 7 but this allows for the inclusion of a variable instead of a pre-defined string).

Hope this helps Have fun! )

Alex Middleton

 

[Hargreavesk]

Alex,

I will give it a try and let you know.

Thankyou for all your help.

Kate

 

[Hargreavesk]

Alex,

This works perfectly. Thankyou, Thankyou, Thankyou, Thankyou.......

Kate )

 

[AlexMidd]

My pleasure. Have fun! )

Alex Middleton