Excel : ignore letters in cell during calculation 4

[Guthro]

Assuming cell a1 = 40 and b1 = 70cl
How can I make a calculation with a result in c1 using the figures in a1 and b1 but ignore the letters "cl" in b1 ?

Thanks.



My Feeblegirl.com Forum boards for mmorpgs, sport, fun, politics...

http://www.jediknightsforum.com

 

[SkipVought]

Hi,

What's the business case for this exersize?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Guthro]

Our company sells alcohol and the recent budget increase has created the need for me to update my spreadsheet pricelists with several thousand products.

The formula for the increase is duty increase x alc.vol x size.
Eg. 0.85 (the budget increase) x 40 (% alc vol) x 70cl (size of bottle.
My spreadsheet has all the products listed with cl, L, ml, etc. against the size. I have removed the % from the volume column but removing the size will look awful when printing out pricelists.
I was hoping to work out the new duty rates without having to strip all the sizes.

My Feeblegirl.com Forum boards for mmorpgs, sport, fun, politics...

http://www.jediknightsforum.com

 

[SkipVought]

There's no way to do this, that I know of, with native Excel features.

Paste this function into a MODULE in the VB Editor (alt+F11).

Function RemAlpha(strS As String)
     Dim re As Object ' object to hold Regular Expression object
    
     Set re = CreateObject("VBScript.RegExp") ' late bind to RegExp object so no need to reference in application
    
     With re
         .Global = True ' find all matches not just first
         .MultiLine = True ' over multiple lines
         .IgnoreCase = True ' whether upper or lower case (more relevant for alpha char matching)
         .Pattern = "[A-Z]" ' regular expression for ALPHA range
         RemAlpha = .Replace(strS, "") ' set return value to value of strS where everything matched by the pattern is replaced with ""
     End With
  
End Function

Use like any other spreadsheet function.
[tt]
C1: =a1*RemAlpha(b1)
[/tt]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Guthro]

a1=20
b1=20cl
c1=a1*RemAlpha(b1)
Results in #VALUE!

The user defined function seems to be available to my spreadsheet. What am I doing wrong ?

 

[SkipVought]

When you toggled to the VB Editor, did you INSERT a MODULE before pasting in the function?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Guthro]

Yes.
Alt+F11
Insert > Module
Pasted your code.
Used the save and closed the editor.

It shows as a function in the user defined list.

I'm using Excel from Office 2000. Ver.9.

 

[Guthro]

Is there a global setting somewhere that needs to be set to allow VB Script ?

 

[Guthro]

Sorry for replying to my own threads, I don't know how to edit them.
SkipVought, I've tried the same script on my home pc setup now and it works fine. It must be something to do with the setup of my old machine.

Thanks for your time and effort.
Much appreciated.

 

[byundt]

If the numbers are always on the left, then you can extract them with a formula like:
=MAX(IF(ISNUMBER(--LEFT(B1,{1,2,3,4,5})),--LEFT(B1,{1,2,3,4,5})))
This formula allows up to 5 digits (including a decimal point).

You can combine this expression with the other parts of your calculation:
=A1*C1*MAX(IF(ISNUMBER(--LEFT(B1,{1,2,3,4,5})),--LEFT(B1,{1,2,3,4,5})))

Brad

 

[Guthro]

Interesting solution Brad.
I'll look at that too.
Thanks.

 

[GlennUK]

A simpler RemAlpha:

Function RemAlpha(strS As String)
     RemAlpha = Val(strS)
End Function

... if the letters are always after the numbers.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[byundt]

Guthro,
It is worth noting that all the suggestions made so far will have difficulty with hyphenated entries like "1-1/2qt".

SkipVought's function returns text "1-1/2", but when you use it in a formula Excel tries to be helpful and converts it into a date. Since dates increment by 1 for each day since January 0, 1900, the result is a huge number like 37257.

My array formula is equally daft, returning 39448.

GlennUK's simpler function returns 1.

I've patched up SkipVought's code to handle that possibility below.

Function RemAlpha(strS As String) As Double
     Dim re As Object ' object to hold Regular Expression object
     Dim sVal As String
     Set re = CreateObject("VBScript.RegExp") ' late bind to RegExp object so no need to reference in application
    
     With re
         .Global = True ' find all matches not just first
         .MultiLine = True ' over multiple lines
         .IgnoreCase = True ' whether upper or lower case (more relevant for alpha char matching)
         .Pattern = "[A-Z]" ' regular expression for ALPHA range
         sVal = .Replace(strS, "") ' set return value to value of strS where everything matched by the pattern is replaced with ""
         RemAlpha = CDbl(Application.Evaluate(Application.Substitute(sVal, "-", "+")))  'Convert to a number
     End With
     Set re = Nothing
End Function

Brad

 

[combo]

You could try to rework your data:
1. reformat b1 to #" cl",
2. remove 'cl' from cells (for more than one cell select all and do search (for 'cl') and replace (by nothing, '').
The data will display units but will remain number.

combo

 

[byundt]

Guthro,
Here is my array formula patched up to tolerate data like "1-1/2qt"

=MAX(IF(ISERR(--LEFT(TRIM(SUBSTITUTE(B1,"-"," ")),{1,2,3,4,5})),,--LEFT(TRIM(SUBSTITUTE(B1,"-"," ")),{1,2,3,4,5})))

It's still an array formula, so remember to Control + Shift + Enter.

Brad

 

[Guthro]

Thanks Brad and the other guys who spent time and replied here, very useful help.
I use Excel alot but it does have a habit of throwing curved balls at me !

Cheers.

 

[AnotherHiggins]

At last! A use for a formula I built a couple of weeks ago before I realized that that question was about access.

This is also an array formula. It's advantage is that it will work against any number of characters. That probably won't really come into play here since the OP is unlikely to have more than the 5 characters that Brad's formula will handle. Still, I figured I'd throw in another solution (especially since it actually answers the question in this thread, unlike the thread in which I originally posted it):

[tab][COLOR=blue white]=LEFT(B1, MATCH(FALSE, ISNUMBER(VALUE(MID(B1, ROW(INDIRECT("1:" & LEN(B1))), 1))), 0) - 1)[/color]

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[byundt]

John,
I'm not getting past the hyphen with your formula. Do you have a tweak that overcomes that issue?

I noticed that the VALUE("1 1/2") returned 1.5 as desired. You could use SUBSTITUTE to change the hyphen to a space, but the MATCH isn't finding the right breakpoint.

FWIW, if you use an array constant like {1,2,3,4,5,6} you can reduce the level of nesting by three. This benefit may be important since you are already at the maximum for Excel 2003.

Brad

 

[lionelhill]

Guthro, I really think you're trying to do the wrong thing.

The cl, ml, L bit is part of the number. 1000mL should be interpreted numerically as the same number as 1L.

You would do much better to split this into multiple columns, and hide the ones you don't want to print (as combo suggested, but with the idea of formatting).

For instance, you could have two columns containing the data you want to print (number and unit), with no visible cell border between them, and appropriate justification so that they look like a single entity for your customers.

After this, you would have a hidden column containing number * multiplier derived from the unit; the multiplier can be got by a simple vlookup to find the unit in a table of units and what they correspond to. This hidden cell would then give you a volume converted to a standard unit (say mL) which would be valid for calculations.

 

[AnotherHiggins]

John,
I'm not getting past the hyphen with your formula. Do you have a tweak that overcomes that issue?

Not off the top of my head. I didn't account for slashes since that wasn't a requirement of the OP.

FWIW, if you use an array constant like {1,2,3,4,5,6} you can reduce the level of nesting by three. This benefit may be important since you are already at the maximum for Excel 2003.

One could easily free up a nesting level in my formula by replacing VALUE([thing]) with [thing]*1.

As I said, the advantage of the formula I posted is that it will work no matter the length of the string.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.