Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

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.

OpenOffice.org

(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
share|improve this answer
    
+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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.