# Convert <x ug/kg to <x mg/kg MS Excel 2007 macro

I have to interpret data for scientific reports. We report the data all in the same units, however, the lab sends the data in different units.

I would like to know how to make a macro that you can apply to a selection of cells or columns to convert the result (divide the number by 1000).

The tricky part of this is that the numbers are often presented as ">5" in which case I would need the macro to change it to ">0.005".

As my data will often comprise upwards of 50 columns, it really needs to be a macro so that I could highlight a column and click the macro. It would then replace the contents of the cells with the revised figures.

-

If there's at most one character there, then for the value in `A1`, the following formula will do the trick:

``````=IF( ISNUMBER(A1), A1/1000, LEFT(A1,1) & MID(A1,2,LEN(A1)-1)/1000 )
``````

That is: if it's a plain number, then simply divide by 1,000 using `A1/1000`. Otherwise copy the first character using `LEFT(A1, 1)`, and use `&` to concatenate the remaining characters from position 2 up to the end given by `MID(A1, 2, LEN(A1)-1)` and divided by 1,000.

(To see the decimals after dividing by 1,000, you might need to set the formatting of the result cells to actually show the decimals. If not, then `0.004` might show as `0` instead.)

I cannot test this as a macro (OpenOffice.org has a different syntax), but maybe this gets you started. Totally based on some quick Google-fu to translate the above though:

``````Function DivideByThousand(s)
If Application.WorksheetFunction.IsNumber(s) Then
DivideByThousand = s / 1000
Else
DivideByThousand = Left(s, 1) & Mid(s, 2, Len(s)-1)/1000
End If
End Function

Sub RecalculateSelection
Dim cell As Excel.Range
For Each cell In Selection
cell.Value = DivideByThousand(cell.Value)
Next cell
End Sub
``````
-
+1. You might need to change the semicolons (;) to commas (,) depending on your locale. – Mike Fitzpatrick Dec 8 '10 at 4:10
@Mike, good point. I actually tested using OpenOffice.org so it might be better to use a comma anyhow. If I remember correctly then Excel always accepts one no matter what language is used, and I guess that's the comma then? – Arjan Dec 8 '10 at 6:25
Thanks, thats greatm but as my data will often comprise upwards of 50 columns, it would still be very time consuming to go down this route. The bennifit of the macro would be that I could highlights a column and click the macros and it would replace the contents of the cell with the revised figure. Thanks chaps – James Dec 8 '10 at 12:02
Ah my bad, @James. Maybe my edit can help you get started? I've NOT tested it! And, anyone who fixes (or improves) the macro, please copy it into your own answer. I'll gladly delete this then! – Arjan Dec 8 '10 at 12:53
Thanks guys, I will have a play aroud and let you know how I get on – James Dec 8 '10 at 17:14