Hank,
all your samples in post 14 have an "M" in the next column. I'm
guessing that in fact some might have "µM"? Your existing code just bypasses those cells but if such a row contained "1.0E-4,1.0E-5,1.0E-6,1.0E-7", shouldn't it be converted into "0.0001,0.00001,0.000001,0.0000001" along the lines of your example in post 1?
If so, could I suggest this modification of Rick's code.
If you need further help, please provide a sample of data that shows the full
variation that can occur with your data as well as the expected results.
Rich (BB code):
Sub Break_String_Test_v2()
Dim X As Long, Cell As Range, Rng As Range, Parts() As String
Set Rng = Range("A1", Cells(Rows.Count, "A").End(xlUp))
For Each Cell In Rng
Parts = Split(Cell.Value, ",")
For X = 0 To UBound(Parts)
Parts(X) = IIf(Cell.Offset(, 1).Value = "M", 1000000, 1) * Parts(X)
Next
Cell.Value = Join(Parts, ",")
Next
Rng.Offset(, 1).Value = "µM"
End Sub
Hi Rick and Peter,
You're both correct with the two points you've made in the last posts. I suppose I started slowly, just trying to figure out how to change from scientific to decimal.
The system I work in has units of either µM or µg/ml and incoming worksheets up until recently were always decimal though the units could be non-standard. I worked out some very long winded method to deconstruct dose response concentrations for non standard unit, do the necessary maths and then join them all up through concatenation. Recently scientific dose responses started to appear and my code could not cope with them and breaking them down again just seemed too time consuming, so I can here and asked the questions.
So items like these:
[TABLE="width: 320"]
<tbody>[TR]
[TD]1.0E-4,3.162E-5,1.0E-5,3.1625E-6[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD]1.0E-4,3.162E-5,1.0E-5,3.1625E-6[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD]1.0E-4,3.162E-5,1.0E-5,3.1625E-6[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD]1.0E-1,1.0E-2,1.0E-3,1.0E-4[/TD]
[TD]mM[/TD]
[/TR]
[TR]
[TD]1.0E4,1.0E3,1.0E2,1.0E1[/TD]
[TD]nM[/TD]
[/TR]
[TR]
[TD]10,1,0.1,0.01[/TD]
[TD]mM[/TD]
[/TR]
[TR]
[TD]1.0E-4,1.0E-5,1.0E-6,1.0E-7[/TD]
[TD]g/ml[/TD]
[/TR]
[TR]
[TD]1.0E-1,1.0E-2,1.0E-3,1.0E-4[/TD]
[TD]mg/ml[/TD]
[/TR]
[TR]
[TD]1.0E-4,3.162E-5,1.0E-5,3.1625E-6[/TD]
[TD]mg/ml[/TD]
[/TR]
[TR]
[TD]1.0E-4,3.162E-5,1.0E-5,3.1625E-6[/TD]
[TD]mg/ml[/TD]
[/TR]
[TR]
[TD]1.0E4,1.0E3,1.0E2,1.0E1[/TD]
[TD]ng/ml[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
could be changed to this:
[TABLE="width: 300"]
<tbody>[TR]
[TD]100,31.62,10,3.1625[/TD]
[TD]µM[/TD]
[/TR]
[TR]
[TD]100,31.62,10,3.1625[/TD]
[TD]µM[/TD]
[/TR]
[TR]
[TD]100,31.62,10,3.1625[/TD]
[TD]µM[/TD]
[/TR]
[TR]
[TD]100,10,1,0.1[/TD]
[TD]µM[/TD]
[/TR]
[TR]
[TD]10,1,0.1,0.01[/TD]
[TD]µM[/TD]
[/TR]
[TR]
[TD]10000,1000,100,10[/TD]
[TD]µM[/TD]
[/TR]
[TR]
[TD]100,10,1,0.1[/TD]
[TD]µg/ml[/TD]
[/TR]
[TR]
[TD]100,10,1,0.1[/TD]
[TD]µg/ml[/TD]
[/TR]
[TR]
[TD]0.1,0.03162,0.01,0.0031625[/TD]
[TD]µg/ml[/TD]
[/TR]
[TR]
[TD]0.1,0.03162,0.01,0.0031625[/TD]
[TD]µg/ml[/TD]
[/TR]
[TR]
[TD]10,1,0.1,0.01[/TD]
[TD]µg/ml[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
with this:
Code:
Sub Break_String_Test()
Dim WrdArray() As String
Dim text_string As String
' code to deconstruct concentration range when units are not standard µM or µg/ml
' A Johnston with the helo of Rick and Peter
' https://www.mrexcel.com/forum/excel-questions/1026709-array-numberformat-need-change-2.html
Dim i As Long
Dim rCell As Range
Dim rRng As Range
Dim LR As Long ' length
LR = Range("A50000").End(xlUp).Row
Set rRng = Sheet1.Range("A1:A" & LR)
For Each rCell In rRng.Rows
If rCell.Offset(0, 1).Value <> "µM" Or rCell.Offset(0, 1).Value <> "µg/ml" Then ' units have to be either µM or µg/ml
text_string = rCell.Value ' convert cell value to string, needed?
WrdArray() = Split(text_string, ",") ' Spilt divides string with "," as the separator
' Molar concentrations to µM
For i = LBound(WrdArray) To UBound(WrdArray)
If rCell.Offset(0, 1).Value = "M" Then
WrdArray(i) = WrdArray(i) * 1000000
ElseIf rCell.Offset(0, 1).Value = "mM" Then
WrdArray(i) = WrdArray(i) * 1000
ElseIf rCell.Offset(0, 1).Value = "nM" Then
WrdArray(i) = WrdArray(i) / 1000
' g/ml concentrations to µg/ml
ElseIf rCell.Offset(0, 1).Value = "g/ml" Then
WrdArray(i) = WrdArray(i) * 1000000
ElseIf rCell.Offset(0, 1).Value = "mg/ml" Then
WrdArray(i) = WrdArray(i) * 1000
ElseIf rCell.Offset(0, 1).Value = "ng/ml" Then
WrdArray(i) = WrdArray(i) / 1000
Else
End If
Next i
text_string = Join(WrdArray, ",") ' Joins the contents of the array to the String
rCell = text_string ' text string replaces the contents of the concentration range
' label units either µM or mg/ml
If Right(rCell.Offset(0, 1), 1) = "M" Then
rCell.Offset(0, 1).Value = "µM"
Else
rCell.Offset(0, 1).Value = "µg/ml"
End If
Else
End If
Next rCell
End Sub
Needs more testing as I've notice the decimal mM line has not been multiplied by 1000 though it has converted the units to µM.
Hope all is good with you.
Thank you for your help.
Hankj
