Extract just numbers from the cell and multiply them

[subbby]

I have columns that contain data similar to this :

• 3/4" STL. PL 17 7/8" X 16"
• 3/4" STL. PL 118 1/4" X 30 1/8"
• 5/8" STL. PL 94 1/2" x 17 3/8"

The code has to :

Answer to first one :
it should multiply: 3/4 * 17 7/8 * 16
second one :
5/8* 94 1/2 * 17 3/8

1. Extract just the numbers and multiply it.
2. Display this answer in adjoining row.
3. All columns are in same format except that the numbers keep changing. Spacing between the numbers and the letters is constant.
4. The code probably will have to check if there is a letter or a number after the space. Eg. 94 ½” vs STL. PL
5. Ignore the inches symbol ("). ignore the multiplication symbol (x)

How to proceed ?
Is this how something to be done with LEN command ?

[Richard Buttrey]

Oh dear!

Sounds like a real tricky one.

I can't help thinking that the easier solution would be to ensure your data is captured in a more sensible format if you wish to manipulate it. Any other solution is going to involve an awful lot of string slicing logic and will probably mean a user defined function at the end of the day.

Where does this data come from?

[subbby]

Oh dear!

Where does this data come from?

Hello..

This is a part list of a coal breaking machine. So each example i gave is a plate size. first one will be : steel plate (STL. PL) .. 0.75" thickness and length and breadth are 17 7/8" and 16" respectively.

And I have millions of such data. Very time consuming to rewrite them in standard decimal format.

Any approach you familiar with ? I mean I can try working too...

[AlKey]

Maybe this?

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(A2),CHAR(34),""),"STL. PL","*"),"X","*"))

	A	B
2	3/4" STL. PL 17 7/8" X 16"	3/4 * 17 7/8 * 16
3	3/4" STL. PL 118 1/4" X 30 1/8"	3/4 * 118 1/4 * 30 1/8
4	5/8" STL. PL 94 1/2" x 17 3/8"	5/8 * 94 1/2 * 17 3/8

[subbby]

Maybe this?

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(A2),CHAR(34),""),"STL. PL","*"),"X","*"))

]

This one is just showing up as 3/4 * 17 7/8 * 7 1/8. It isnt multiplying and giving a value.

Is tehre a way to make it multiply ?

[slx]

This extracts all 3 measures out of the string. You will have to do the conversion of whole numbers and fractions also replace the special characters ½ with the actual 1/2.

Please Login or Register  to view this content.

[AlKey]

I didn't think you want to go that far.LOL

[subbby]

This extracts all 3 measures out of the string. You will have to do the conversion of whole numbers and fractions also replace the special characters ½ with the actual 1/2.

Hi...

This kind of worked...

Range B1 : its spitting out in date format
in Range D1 : the inches symbol also accompanies. is there a way I could get rid of that ?

[alvin-chung]

Hi, if this is a one-time task you might consider using Text-to-Columns to gradually break-down into each individual value then sum/multiply respective columns accordingly



_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

[slx]

Date format is done on the worksheet, if u set the whole column to format general. if you see the quotation marks then the spacing is off, you see the - 1, that's what you change. You change it to -2 or + 1 to move the spacing left or right, increase or decrease the number to move it farther.

[subbby]

hello slx.

it is still giving teh same answer with the inches symbol on.
I tried this

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

still the same problem

[slx]

Change to this, i am guessing you have a lot of spaces after your quotation marks

Please Login or Register  to view this content.

[subbby]

Change to this, i am guessing you have a lot of spaces after your quotation marks

There is no space at all SLX.


3/4" STL. PL 44 1/4" X 7 1/8".. its posting D1 as 7 1/8"

[subbby]

tried this

Please Login or Register  to view this content.

it worked for above example but did not work for

1" STL. PL 10 15/16" X 4 13/16"

[slx]

This will remove the quotation marks

Please Login or Register  to view this content.

[subbby]

Thanks SLX. that worked...

REPUTATIONS ADDED

[subbby]

Final code in my program :

Please Login or Register  to view this content.

Worked well and gave desired answers

[slx]

I noticed that excel will pick up "3 1/2" or "12 5/8", properly as fractional if you preset the columns to be fractional. Format Cells > Number > Fraction > {choose your accuracy}. You can include the final formula of =product() in the last column