VB Excel function code guru question, help

[icm63]

I have a number A

A:846

I have a number like

B: 14000.56
C: 2.51
D: 524.5
E: 25.25

As number A is 3 digits and a integer I would like to work out factor 'X'.
X is the factor to convert B,C,D,E to three digits integer.

Like
B: 140 , X = 0.001
C: 251, X = 100
D: 526, X = 1
E: 252, X = 10

Any idea how to do a function that returns X ????

Thanks

 

[Joe4]

Welcome to the Board!

First, I think you have a typo. I think B should be 0.01.

You do not need VBA to do this. Let's say that "A" is in cell A1, and B is in cell A2. Then this formula will return that you want:

=10^(LEN(INT(A$1))-LEN(INT(A2)))

Of course, you could create a User Defined Function in VBA to do this also, if you really wanted.

 

[Rick Rothstein]

Do you really need the multiplication factor or did you want it strictly for use in converting those numbers to three digits? If getting those three-digit numbers is your sole goal, then here is a formula to produce the three-digit numbers directly...

=LEFT(SUBSTITUTE(B1,".","")&REPT(0,LEN($A$1)),LEN($A$1))

 

[AkaTrouble]

no offense to original poster

i never really understood the question ( me at fault rather than poster)

but have to say that this forum is my only recent experience that literally blows my mind

the reply i totally understood and still i don't understand the question ... how does that work

i know this is off topic a little and not totally relevant to post .. but

sorry had to be said the advice and help here is incredible

 

[Scott Huish]

I think C should be 524 but if your goal is just to get those numbers, I believe this will work:

=LEFT(SUBSTITUTE(E1&".000",".",""),3)+0

 

[Joe4]

It sounds to me like they want the factor, not the number:

Like
B: 140 , X = 0.001
C: 251, X = 100
D: 526, X = 1
E: 252, X = 10

Any idea how to do a function that returns X ????

 

[Rick Rothstein]

It sounds to me like they want the factor, not the number:

True, but then the OP said "X is the factor to convert B,C,D,E to three digits integer" which made me wonder if that is why he wanted the X... so that he could do the conversion with it once he figured out how to get it. Plus, I wondered about the usefulness of a "free floating" X number in a cell when there can be a lot of cells with them in it and they all differ.

 

[icm63]

True, but then the OP said "X is the factor to convert B,C,D,E to three digits integer" which made me wonder if that is why he wanted the X... so that he could do the conversion with it once he figured out how to get it. Plus, I wondered about the usefulness of a "free floating" X number in a cell when there can be a lot of cells with them in it and they all differ.

Thanks for the responses

I after the 'X' factor , not the number, but I can work that out.

 

[Scott Huish]

Then Joe4's solution should do what you want.