Find/replace scientific notation

[phrankndonna]

Hi,

In my range, I need to find/replace some scientific notation with that same notation in parenthesis. Generally speaking, I'm able to do this relatively easily (e.g., for things such as g/dL). However, certain aspects are simply not converting well in VBA. For instance, the symbol for micro does not seem to go into VBA at all, and 10 to the 6th power does not seem to go well into Excel (nor here)...which is strange since 10 to the 3rd power will superscript. This should be a simple find/replace routine, but it's turned out to not be so simple, at least for me. I hope someone can help with this. Thanks.

What shows up:
106/μL (except the 6 needs to be superscripted)
10³/μL

What I need:
(106/μL)
(10³/μL)

Frank

[phrankndonna]

Hi again,

Actually, after all the formatting in Excel gets done, all the data gets copied to a table in Word. So would it be easier to do this formatting aspect in MS Word?

Frank

[DGagnon]

could you just devide the result by 1000?

[DGagnon]

sorry i misunderstood what you were trying to accomplish,

is your value being calculated by a formula, or is it being typed into the cell like that?

[phrankndonna]

It is generally being copied/pasted from a PDF into Excel. This is just part of the header for some values that follow. There are some calculations done on the values, then the whole thing is copied from Excel into Word. So, I could either do the find/replace in Excel or in Word, it wouldn't really matter. But I whether it's Word or Excel, I encounter the same issue with entering superscript and the micro symbol in VBA. Thanks for your time.

Frank

[DGagnon]

Try this you just need to adjust the range you want to change, the 1 to 15 is the columns, and the 1 in Cells(1,x) is the row.

Sub Macro1()
    For x = 1 To 15
        With Cells(1, x).Characters(Start:=3, Length:=1).Font
            .Superscript = True
        End With
    Next x
End Sub

[MrShorty]

And the micro symbol, which is simply the greek letter mu (our m), can be added in a similar fashion. Enter a lower case m, then add a line in DGagnon's code that will change the character that is in the "m's" location to the symbol font.

Both steps can also be done manually (I expect, anyway, I haven't really done anything like this yet in 2007/2010) as well. In 2002/XP, I do this all the time by:
1) Selecting the desired cell
2) Press F2 to edit the cell
3) Navigate through the text string and select the desired character(s)
4) alt-o, e to bring up the format selection dialog
5) Change font and/or super/subscript settings as desired
6) Hit enter twice to confirm changes.

[phrankndonna]

Thank you both. I will try these tonight and tomorrow at work. I'll post back the results. Thanks again.

Frank