If you are managing an inventory of your electronic components in LibreOffice Calc (open-source alternative to Microsoft Excel) you have probably needed to convert scientific and/or engineering notation to metric at some point. I personally wanted to dynamically generate summaries of each component that would allow me to see it's core parameters at a glance, something like in the example below.

Summary: SMD/SMT: 0402, 10pF, 50V, 5%, C0G

While LibreOffice does have a function that allows one to convert an arbitrary measurement between metric units - function CONVERT, it's return value doesn't come with a prefix.

Field formatting is, unfortunately, way too limited, which means that the only other viable option is a custom function - or "user-defined function" as stated in the official LibreOffice documentation.

To define a custom function you must first open up the Basic IDE:
Tools -> Macros -> Edit Macros

LibreOffice Calc - Edit Macros

Here is my solution for the problem - consisting of two relatively simple functions. First one is used for calculating the exponential value of a number.

Note: I am not familiar with the programming conventions?! in LibreOffice Basic, so I decided to use two underscores for denoting arguments of a function, and a single underscore to denote function variables. Variable names are capitalized because I felt like capitalizing them when writing this utterly "magnificent" - but functional - piece of code.

Function POW(__BASE, __EXP)
	
	If (__EXP = 0) Then
		
		If (__BASE > 0) Then
			POW = 1
		Else
			POW = -1
		End If
		
	ElseIf (__EXP = 1) Then
		
		POW = __BASE
		
	Else
		
		Dim _NEGATIVE As Boolean
		Dim _RESULT   As Double
		
		If (__EXP < 0) Then
			 _NEGATIVE = True
			__EXP      = __EXP * -1
		End If
		
		_RESULT = __BASE
		
		For _I = 2 To __EXP
			_RESULT = _RESULT * __BASE
		Next _I	
		
		If (_NEGATIVE = True) Then
			POW = 1 / _RESULT
		Else	
			POW = _RESULT
		End If
		
	End If

End Function

Second function converts an arbitrary number to it's appropriate metric equivalent.

Function TO_METRIC(__NUMBER, __UNIT)
	
	Dim _CONVERTED   As Boolean
	Dim _MULTIPLES() As Integer
	Dim _PREFIXES()  As String
	Dim _EXP_RES     As Double
	Dim _PROBE_RES   As Double
	
	_MULTIPLES = Array(15, 12, 9, 6, 3, 0, -3, -6, -9, -12, -15, -18)
	_PREFIXES  = Array("P", "T", "G", "M", "k", "", "m", "u", "n", "p", "f", "a")

	For _I = 0 To 11
		
		_EXP_RES   = POW(10, _MULTIPLES(_I))
		_PROBE_RES = __NUMBER / _EXP_RES
		
		If (_PROBE_RES >= 1) Then
			
			TO_METRIC = Str(_PROBE_RES) + _PREFIXES(_I) + __UNIT
			
			_CONVERTED = True
			
			Exit For
			
		End If
		
	Next _I
	
	If (_Converted = False) Then
	
		_EXP_RES   = POW(10, -18)
		_PROBE_RES = __NUMBER / _EXP_RES
		
		TO_METRIC =  Format(_PROBE_RES, "0.00") + "a" + __UNIT
		
	End If
	
End Function

After saving and compiling the code, you should be able to convert any number expressed in scientific or engineering notation to metric using the previously defined "TO_METRIC" function.

Conversion Example