Extractor integer and mantissa parts

[wabbitwabbit]

Using VBA within Excel and having calculated an amount, which can be any value up to 2,000,000.00 I now want to extract the integrer pounds value, that is quite easy BUT extracting to the right of the decimal place is less easy.

For example CalcValue = 5678.96

By extraction -

LenIs = Len(CalcValue)

DecLoc = InStr(1, CalcValue, ".")

ValBox1 = Left(CalcValue, DecLoc - 1)

Sheets("Form").Range("FormBox1") = ValBox1 'Display integer only

P = Mid(CalcValue, DecLoc + 1, 2)

LenP = Len(P)

If LenP = 1 Then P = P & "0" ' I want trainling zeropsd to be literally displayed

Sheets("Form").Range("FormBox1P") = P

There HAS to be an easier way, any VBA blokes on here can help. All I am actually doing is automating a VAT Return form and having derived all the values for all boxes on a VAR Return want to plug the values into a template sheet such that the form as sent by the VAT office can be put through the printer.

Cheers

 

[wabbitwabbit]

Sorry, I mean Integer Extraction,

and I did not mean to enter mantissa which everyone knows is the E part of a floating part number example 3456E23 where 23 is the mantissa :_|

 

[kme]

Although VBA is not something if particularly fluent in:

two logical options come to mind....

1. having derived the integer, can you not subtract that from the original, to leave the fraction?

2. failing that, what about origin no. multiplied by 100, then integer leftmost 2 digits for fraction, and rightmost string length-2 for integer?

Or am I talking dogs danglies?

 

[wabbitwabbit]

Ah!

Me tinks ya may be a genius kme - as in the first option you give. I will try it 2morrow. As you say having derive some numeric string which may be a whole number or one with a decimal by deducting the original integer what will be left if the decimal part.

Thank you kme - a good bit if lateral thinking on your part. I assume that you are familiar with Edward Debono?

Cheers

WW

 

[kme]

No problem my friend.

And yes, I absolutely love lateral thinking puzzles. Such a good mind stretcher.

for any philistines reading - Debono is NOT the lead singer of the German version of U2 ok??

 

[biker mike]

.for any philistines reading - Debono is NOT the lead singer of the German version of U2 ok??

Thats right he's the ex husband of decher