Excel: Convert Oz. to milliliters inside Text
Closed
gugga7
-
Apr 26, 2009 at 08:29 AM
mubashir aziz Posts 190 Registration date Sunday April 12, 2009 Status Member Last seen February 16, 2010 - May 6, 2009 at 04:20 AM
mubashir aziz Posts 190 Registration date Sunday April 12, 2009 Status Member Last seen February 16, 2010 - May 6, 2009 at 04:20 AM
Related:
- Excel: Convert Oz. to milliliters inside Text
- Excel date format dd.mm.yyyy - Guide
- Excel free download - Download - Spreadsheets
- Number to words in excel - Guide
- Excel marksheet - Guide
- How to take screenshot in excel - Guide
2 responses
mubashir aziz
Posts
190
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
165
Apr 28, 2009 at 03:48 AM
Apr 28, 2009 at 03:48 AM
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 April 12, 2009
Status
Member
Last seen
February 16, 2010
165
Apr 28, 2009 at 07:12 AM
Apr 28, 2009 at 07:12 AM
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 April 12, 2009
Status
Member
Last seen
February 16, 2010
165
>
mubashir aziz
Posts
190
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
May 6, 2009 at 04:20 AM
May 6, 2009 at 04:20 AM
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
