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

- - Latest reply: mubashir aziz
Posts
191
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
- May 6, 2009 at 04:20 AM
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?
See more 

2 replies

Best answer
Posts
191
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
209
3
Thank you
Just replace the Oz with millimeters ......................... or you want something else ........

Say "Thank you" 3

A few words of thanks would be greatly appreciated. Add comment

CCM 5744 users have said thank you to us this month

0
Thank you
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.
mubashir aziz
Posts
191
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
209 -
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 .....
mubashir aziz
Posts
191
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
209 > mubashir aziz
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