Excel: Convert Oz. to milliliters inside Text
Closed
gugga7
-
26 Apr 2009 à 08:29
mubashir aziz Posts 190 Registration date Sunday 12 April 2009 Status Member Last seen 16 February 2010 - 6 May 2009 à 04:20
mubashir aziz Posts 190 Registration date Sunday 12 April 2009 Status Member Last seen 16 February 2010 - 6 May 2009 à 04:20
Related:
- 0.2oz to ml
- Excel mod apk for pc - Download - Spreadsheets
- Vat calculation excel - Guide
- Kernel for excel repair - Download - Backup and recovery
- Arrow keys not working in excel - Guide
- Excel clear formatting - Guide
2 responses
mubashir aziz
Posts
190
Registration date
Sunday 12 April 2009
Status
Member
Last seen
16 February 2010
166
28 Apr 2009 à 03:48
28 Apr 2009 à 03:48
Just replace the Oz with millimeters ......................... or you want something else ........
Sorry for not providing more details.
I want to do a mass find/replace from an established list because there are too many Oz values. The conversion list will be like this:
0.1 Oz --> 2.95 ml
0.2 Oz --> 5.91 ml
0.3 Oz --> 8.87 ml
...
30 Oz --> 887 ml
i want run something that will scan this conversion list, get the correct value and replace it in the spreadsheet.
For example :
This bottle is 0.2 Oz and is great.
The function will look into the table, get the corresponding value for 0,2 Oz (5.91 ml) and the output will be:
This bottle is 5.91 ml and is great.
I want to do a mass find/replace from an established list because there are too many Oz values. The conversion list will be like this:
0.1 Oz --> 2.95 ml
0.2 Oz --> 5.91 ml
0.3 Oz --> 8.87 ml
...
30 Oz --> 887 ml
i want run something that will scan this conversion list, get the correct value and replace it in the spreadsheet.
For example :
This bottle is 0.2 Oz and is great.
The function will look into the table, get the corresponding value for 0,2 Oz (5.91 ml) and the output will be:
This bottle is 5.91 ml and is great.
mubashir aziz
Posts
190
Registration date
Sunday 12 April 2009
Status
Member
Last seen
16 February 2010
166
28 Apr 2009 à 07:12
28 Apr 2009 à 07:12
Oh so value of oz you have in a column without oz ...... like in column b
OZ ml
B1= 0.1 <=0.1 = 2.95
B2=0.2 <=0.2= 5.91
B3=0.3 <=0.3 = 8.87
.
.
.
.
B30=30
Or you are multiplying value of 2.95 with 2, 3 and so on ...... .2*2.95 = 5.91 and then 2.95*.3=8.85 ...... bcoz first of all it is necessary to analyze the data and then we'll implement the formula .....
anyway u can send ur file on my email maziz@jgc-descon.com.pk with the link of this page so it will be easy for me to check and give you a suitable solution .....
OZ ml
B1= 0.1 <=0.1 = 2.95
B2=0.2 <=0.2= 5.91
B3=0.3 <=0.3 = 8.87
.
.
.
.
B30=30
Or you are multiplying value of 2.95 with 2, 3 and so on ...... .2*2.95 = 5.91 and then 2.95*.3=8.85 ...... bcoz first of all it is necessary to analyze the data and then we'll implement the formula .....
anyway u can send ur file on my email maziz@jgc-descon.com.pk with the link of this page so it will be easy for me to check and give you a suitable solution .....
mubashir aziz
Posts
190
Registration date
Sunday 12 April 2009
Status
Member
Last seen
16 February 2010
166
>
mubashir aziz
Posts
190
Registration date
Sunday 12 April 2009
Status
Member
Last seen
16 February 2010
6 May 2009 à 04:20
6 May 2009 à 04:20
I"m sending solution of your answer but with some restriction as I worked on it hard but couldn't still sort it out completely .... anyway you can check and may be put some more checks on it ......... pls. do let me know in case of any success .........
Sub Array_Replace()
Dim a(150) As Double
Dim b(150) As Double
Application.ScreenUpdating = False
Columns("A:A").Select
' Don't write .1 Oz as it will not convert it as most of the times you have written 0.1 so I convert 0.1
' Also, if you have written foum2.5 Oz then it will not work as there
' must be a space between text & number foum 2.5 Oz
' This procedures is to control Oz if you have written Oz with number like 2.5OZ
Selection.Replace What:=" OZ", Replacement:="Oz", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="OZ", Replacement:=" Oz", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
For I = 0 To 9 Step 0.1
a(i) = Round(i + 0.1, 1)
b(i) = Round(a(i) * 29.75, 0)
Selection.Replace What:=" " & a(i) & " OZ", Replacement:=" " & a(i) & " Oz" & "/" & b(i) & " ml", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next
Application.ScreenUpdating = True
End Sub
