Hi

i requre a little bit of guidance and help to right a simple function

I have a value in milimeters which i want to pass to a function followed by
a string or value to show that i want the result converting in to

a) meters & cm - lets call it m
b) cms & mm - lets call it cm
c) mm - lets call it mm

even though i passing the mm's to the function as a number i wish it to
display it as a string

thus 1234,m i wish to return as 1 m 23.4 cm
or 1234,cm will return 123 cm 4 mms or 123.4 cms
or 1234 mm will return 1234 mms


I know this should be easy but i just cant seem how i should structure my
login..

was trying to use mod and fix .. i cant see why i can get my head round it..
as i done something similar for inches and passing the factor of the inches
.... ie 1/8th 1/16th etc to it.. to display feet inches and denominator...

please help..

A1=1234

in b1:

=convm(a1,"m")


Function ConvM(ByVal metric As Long, munits As String) As String
Select Case munits
Case Is = "m"
cm = (metric Mod 1000) / 10
m = Int(metric / 1000)
ConvM = Str(m) & " m" & Str(cm) & " cm"
Case Is = "cm"
cm = metric / 10
ConvM = Str(cm) & " cm"
Case Is = "mm"
mm = metric
ConvM = Str(mm) & " mm"
Case Else
ConvM = "Invalid Units"
End Select

End Function

"devo" wrote:

Hi

i requre a little bit of guidance and help to right a simple function

I have a value in milimeters which i want to pass to a function followed by
a string or value to show that i want the result converting in to

a) meters & cm - lets call it m
b) cms & mm - lets call it cm
c) mm - lets call it mm

even though i passing the mm's to the function as a number i wish it to
display it as a string

thus 1234,m i wish to return as 1 m 23.4 cm
or 1234,cm will return 123 cm 4 mms or 123.4 cms
or 1234 mm will return 1234 mms


I know this should be easy but i just cant seem how i should structure my
login..

was trying to use mod and fix .. i cant see why i can get my head round it..
as i done something similar for inches and passing the factor of the inches
.... ie 1/8th 1/16th etc to it.. to display feet inches and denominator...

please help..

I am not sure you are giving us the right example of what you want..

1234m does not equal to 1 M 23.4 CM

nor does 1234cm = 123 cm 4 mm and vise versa..

I did take what I think you meant and put this into a formula (it's not
pretty, but it works) and you don't have to set up a macro or fuction,
so that if you put:

1234mm will convert to cm and mm (123 cm 4 mm)
1234cm will convert it to M and cm (1 M 23.4 cm)

you can play around with this.

=IF(RIGHT(A1,2)="mm",QUOTIENT(LEFT(A1,LEN(A1)-2),10)&" cm
"&MOD(LEFT(A1,LEN(A1)-2),10)&"
mm",IF(RIGHT(A1,2)="cm",QUOTIENT(LEFT(A1,LEN(A1)-2),1000)&" M
"&MOD(LEFT(A1,LEN(A1)-2),1000)/10&" cm"))


--
Bearacade


------------------------------------------------------------------------
Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
View this thread: http://www.excelforum.com/showthread...hreadid=559040

The base units are mm to be converted to m, cm or left as mm.

So we always start with 1234 mm =1.234 m or 1m 23.4 cm = 123 cm 4mm (123.4 cm)

"Bearacade" wrote:


I am not sure you are giving us the right example of what you want..

1234m does not equal to 1 M 23.4 CM

nor does 1234cm = 123 cm 4 mm and vise versa..

I did take what I think you meant and put this into a formula (it's not
pretty, but it works) and you don't have to set up a macro or fuction,
so that if you put:

1234mm will convert to cm and mm (123 cm 4 mm)
1234cm will convert it to M and cm (1 M 23.4 cm)

you can play around with this.

=IF(RIGHT(A1,2)="mm",QUOTIENT(LEFT(A1,LEN(A1)-2),10)&" cm
"&MOD(LEFT(A1,LEN(A1)-2),10)&"
mm",IF(RIGHT(A1,2)="cm",QUOTIENT(LEFT(A1,LEN(A1)-2),1000)&" M
"&MOD(LEFT(A1,LEN(A1)-2),1000)/10&" cm"))


--
Bearacade


------------------------------------------------------------------------
Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
View this thread: http://www.excelforum.com/showthread...hreadid=559040

1234cm = 12m 34 cm NOT 1m 23.4 cm.

"Bearacade" wrote:


I am not sure you are giving us the right example of what you want..

1234m does not equal to 1 M 23.4 CM

nor does 1234cm = 123 cm 4 mm and vise versa..

I did take what I think you meant and put this into a formula (it's not
pretty, but it works) and you don't have to set up a macro or fuction,
so that if you put:

1234mm will convert to cm and mm (123 cm 4 mm)
1234cm will convert it to M and cm (1 M 23.4 cm)

you can play around with this.

=IF(RIGHT(A1,2)="mm",QUOTIENT(LEFT(A1,LEN(A1)-2),10)&" cm
"&MOD(LEFT(A1,LEN(A1)-2),10)&"
mm",IF(RIGHT(A1,2)="cm",QUOTIENT(LEFT(A1,LEN(A1)-2),1000)&" M
"&MOD(LEFT(A1,LEN(A1)-2),1000)/10&" cm"))


--
Bearacade


------------------------------------------------------------------------
Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
View this thread: http://www.excelforum.com/showthread...hreadid=559040

Thanks for helping us out..

Yeah that seems to work really well..

Much appreciated... !

"devo" wrote in message
...
Hi

i requre a little bit of guidance and help to right a simple function

I have a value in milimeters which i want to pass to a function followed
by a string or value to show that i want the result converting in to

a) meters & cm - lets call it m
b) cms & mm - lets call it cm
c) mm - lets call it mm

even though i passing the mm's to the function as a number i wish it to
display it as a string

thus 1234,m i wish to return as 1 m 23.4 cm
or 1234,cm will return 123 cm 4 mms or 123.4 cms
or 1234 mm will return 1234 mms


I know this should be easy but i just cant seem how i should structure my
login..

was trying to use mod and fix .. i cant see why i can get my head round
it.. as i done something similar for inches and passing the factor of the
inches ... ie 1/8th 1/16th etc to it.. to display feet inches and
denominator...

please help..