+ Reply to Thread
Results 1 to 3 of 3

How to multiply each number in a column of cells when the data is mixed with other text

  1. #1
    Registered User
    Join Date
    11-25-2014
    Location
    Ningbo
    MS-Off Ver
    Ms office 2010 Enterprise
    Posts
    2

    How to multiply each number in a column of cells when the data is mixed with other text

    Hi there forum!

    I am having this problem for quite a bit.
    I have the product inventory of a Chinese company and I am asked to translate the information to English, together with converting all the measurements into the American style (from centimeters into inches, kilograms into pounds)
    It would be much easier if the data was better organized but the best I could do to work it out looks like this: Size: L122 * W90 * H106cm
    There is more than a hundred of these so its not efficient at all for me to do the measurements one by one.
    How can I create a formula to do the measurements for me?
    I have attached a sample spreadsheet on this post so you can take a look at it. Any help is highly appreciated.

    Cheers,

    Orkun
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor ranman256's Avatar
    Join Date
    07-29-2012
    Location
    Kentucky
    MS-Off Ver
    Excel 2003
    Posts
    1,177

    Re: How to multiply each number in a column of cells when the data is mixed with other tex

    I would make 3 columns, L, W, H to separate the values:
    L2 = MID(A1,2,FIND(" ",A1,2)-1)
    W2 =MID($A1,FIND("W",$A1,2)+1,FIND(" ",$A1,1)-2)
    etc.
    Then convert the values in another cell to English units.

  3. #3
    Registered User
    Join Date
    11-25-2014
    Location
    Ningbo
    MS-Off Ver
    Ms office 2010 Enterprise
    Posts
    2

    Re: How to multiply each number in a column of cells when the data is mixed with other tex

    Thanks but that still makes it hard in some cases. Is there any way to multiply numbers in a column when they are mixed with letters? I mean teach Excel just to consider numbers in a function and nothing else

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Moving only Text in a Mixed Text/Number Column to different Row/Colum
    By eas84 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-07-2012, 12:37 PM
  2. Match number with mixed text num in Column
    By aljanga in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-27-2009, 03:33 PM
  3. ZeroPadding Mixed Number, Text, Number Content in Cell
    By Sbubendorf in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-18-2008, 05:52 PM
  4. Merging mixed (number/text) data into Word
    By camilof in forum Excel General
    Replies: 0
    Last Post: 06-09-2008, 06:52 PM
  5. Replies: 1
    Last Post: 10-22-2005, 02:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1