Search : in
By :

Excel: Convert Oz. to milliliters inside Text

Last answer on May 6, 2009 9:20:07 am BST gugga7, on Apr 26, 2009 1:29:15 pm BST 
 Report this message to moderators

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?

Configuration: Mac OS X
Safari 528.16

Best answers for « Excel: Convert Oz. to milliliters inside Text » in :
[Excel]changing cell formula to text Show [Excel]changing cell formula to text Issue Solution Notes Issue Consider that I have: In cell A4, it contains a formula =Sum(A1:B3)+A3/B2. How to extract this formula in cell A6 as a string of text? That is ... I want cell A6...
Download ABC Amber Excel Converter Show Do you know that your Excel spreadsheet can be converted into any format? Only ABC Amber Excel Converter can gather these many conversions into a single application. ABC Amber Excel Converter is a program to convert your XLS files into several...
Excel Leading Zero Format Show
Converting a URL to a clickable hyperlink ShowConverting a URL to a clickable hyperlink With regular expressions, it is possible to transform any hyperlink URL of the form http://URL (or ftp://URL) or beginning with www. :
Converting a 32-bit integer into IP ShowConverting a 32-bit integer into IP Number to convert: 3265917058 Binary representation 11000010 10101001 11110000 10000010 - 3265917058 00000000 00000000 00000000 11000010 - 3265917058 >> 24 ( 194 ) 11000010 10101001 11110000...
How to convert Excel into PDF? ShowHow to convert Excel into PDF? Here is a small tips about how to convert your excel files into PDF for your presentation. Step 1 PDF995 is software that gets installed on your computer which enables you to print any sources of document to...
Download Free PowerPoint/PPT to Pdf Converter ShowIt is frequent to convert Word, Excel or image files to PDF format. But it is not very common to convert a PowerPoint file to the PDF format. PowerPoint / PPT to Pdf Converter is an application allowing to convert easily and quickly PowerPoint...
Download Free Excel/Xls to Pdf Converter Show
Download DBF to Excel Converter ShowDescription The application is designed by WhiteTown Software. DBF to Excel is a tool that allows you to convert your file easily. Simple and easy to use, the application has been awarded from various places. Offering an intuitive interface, the...
Ask your question Reply

1

mubashir aziz, on Apr 28, 2009 8:48:13 am BST

Just replace the Oz with millimeters ......................... or you want something else ........

   
Reply to mubashir aziz

2

gugga7, on Apr 28, 2009 9:30:16 am BST

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.

   
Reply to gugga7

3

mubashir aziz, on Apr 28, 2009 12:12:44 pm BST

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

   
Reply to mubashir aziz

4

 mubashir aziz, on May 6, 2009 9:20:07 am BST
  • +1

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



Never ashamed to get or give Advise.
Muhammad Mubashir Aziz , Lodhran

   
Reply to mubashir aziz
Ask your question Reply
They need your help