 # Excel: Convert Oz. to milliliters inside Text [Closed]

-
Posts
191
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
-
Hello,

I've been looking for a solution for the following issue:

I have a very long excel list of product description that contain product with OZ value.

For example:

Bottle A is 4 Oz

I would like to convert the OZ to milliliters so that I could have

Bottle A is x milliliters

How can I do a mass find and replace for that value knowing that it is included within text?

## 2 replies

Posts
191
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
215
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.
Posts
191
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
215
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 .....
Posts
191
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
215 >
Posts
191
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010

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``` 