Extract Values and percentages from text string

**Andy748** · 04-01-2007, 02:58 PM

Hi, I have a tricky problem to solve:
I need to calculate a blended interest rate (like APR) that gets one value from a text string.

The column looks like this:
Completion £799,
Arrangement £499,
Arrangement 1.50% Advance,
Arrangement £499,
Arrangement £499,
Arrangement £499,
Completion 1.25% Advance (Min £300.00),
Arrangement £799,
Arrangement £799,
Arrangement 1.50% Advance,
Arrangement 2.00% Advance (Min £599.00),

To work out the rate I may need to create two new columns, one containing £ and the other % values.
I then use an IF statement in the blended rate cell to select the value.
Any ideas?

**mikerickson** · 04-01-2007, 05:56 PM

You may be interested in this UDF. It can be used in spreadsheet formulas.

getFirstNumber returns the number matching the first numeral in a string

getFirstNumber("xx12yy") =12
getfirstnumber("xx1.2yy33")=1.2
Be aware of "-" and "."
getfirstNumber("xx. 3")=.3
getFirstNumber("aa-34")=-34

If the optional second argument (percentCorrection) is set to True, it checks for a "%" anywhere after the first numeral and returns the decimal expression. percentCorretion defaults to False.

getFirstNumber("aa30%") = 30
getFirstNumber("aa30%", False) = 30
getFirstNumber("aa30%", True) = .3

getFirstNumber("bb24xyz12%x",True) = .24

I hope it helps.

Please Login or Register  to view this content.

Extract Values and percentages from text string

LinkBack

Thread Tools

Rate This Thread

Display

Extract Values and percentages from text string

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions