inches to mm

[Edgewise]

Hi everyone,
I'm working a fairly large database of parts in excel. Typically most of the data in the spreadsheet is metric dimensions (mm). However, occasionally I'll come across a imperial drawing which needs to be added. I'm looking for something that can convert between mm and inches quickly (manual conversion is too slow).
Basically something that when I enter a value followed by the ' " ' (inch symbol) excel will replace my entry with the mm equiv. Or something that is similar.
Something else that could work is simply button that will convert the entire spreadsheet to either imperial or metric (as long as entries can still be added to either system).


Thanks,
edgewise

 

[melone]

Write some VBA code that looks for the " symbol, and divide by 25.4. Are all of your "numbers" in a constrained space, i.e. columns?

 

[gearguru]

melone,
you meant multiply by 25.4 correct?

 

[jghrist]

Put 25.4 in a cell. Edit|Copy or Ctrl-C to get the value 25.4 into the Windows clipboard. Highlight all the cells with inches and Edit|Paste|Special|Values|Multiply. This will convert all highlighted inches to mm. If other inch values are input later, you can Edit|Paste|Special|Multiply again without copying the 25.4 as long as nothing else has been copied since the first copy (the 25.4 stays in the clipboard).

Or record a macro to do the pasting with a keystroke. One caution with the macro approach is that you can't undo it; if you do the manual paste operation and screw up by multiplying the wrong things, you can recover easily.

 

[melone]

Multiply, right..... Brain fart

 

[Edgewise]

melone,
All of my data is within 4 columns.

I'm thinking it will be my best option to do something with vba code. However, my experience with vba is very limited. I do however have experience with other programming languages, is someone able to give me some direction on this, or perhaps point me to some information I might find on the net?

Thanks

 

[IRstuff]

You can do that, but why not simply use a 5th column to do the conversion? e.g.,

=if(right(d1,1)=char(34),d1*25.4,d1)


Otherwise, you can try:

Private Sub Worksheet_Change(ByVal Target As Range)
If Right(ActiveCell, 1) = Chr(34) Then ActiveCell.Formula = Left(ActiveCell, Len(ActiveCell) - 1) * 25.4

End Sub


TTFN

 

[joerd]

Probably better (haven't tried it though):
Private Sub Worksheet_Change(ByVal Target As Range)
If Right(Target.Value, 1) = Chr(34) Then Target.Formula = Left(Target.Value, Len(Target.Value) - 1) * 25.4

End Sub

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[Edgewise]

A thanks goes out to everyone that posted.
I haven't had a chance to give this a try at work yet, but I gave it a quick try at home and it seems to do exactly what I need.

Thanks again.