Extractor integer and mantissa parts

Help Support Talk Electrician Forum:

W

wabbitwabbit

Guest
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

 
W

wabbitwabbit

Guest
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 :_|

 
Joined
Mar 7, 2008
Messages
9,994
Reaction score
0
Location
In the office again.
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?

 
W

wabbitwabbit

Guest
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

 
Top