Vlookup formula - return only numbers for cells containing text and numbers

Please can somebody advise if this is possible....

I want my vlookup formula to return only the numbers from a cell containing text, a currency sign, numbers and digit grouping commas

The possibilities in the data range (table_array) cells are "we call/ we deliver/ we return £1 - £100,000,000.00" e.g. "we deliver €150,000.00" or "We return £9,700,000.00" and also "Below MTA". The amounts can be anything up to 500mill.

The Vlookup formula needs to do the following:
1. return only numbers
2. We deliver and We return needs to be a negative amount
3. We call needs to be a positive amount
4. Below MTA needs to return 0

E.g. if the data was "We deliver €150,000.00" I want the vlookup to return -150,000.00 (the format doesnt really matter as I can change this once the number has been returned), for "We Call $7,500,000.00" I want 7,500,000.00 returned and for "below MTA" I want 0 returned.

Can any Excel boffin held with this? Any help is gratefully received. I have a manual work around for this but it would be magnificant if it could be automated.

Cheers






Any suggestions gratefully appreciated.

Try this for Cell B2. It assumes all non-'below MTA' cells will necessarily start with 'we deliver/call'/return '

=IF(B2="below MTA",0,IF(OR(MID(B2,FIND(" ",B2,1)+1,FIND(" ",B2,4)-4)="deliver",MID(B2,FIND(" ",B2,1)+1,FIND(" ",B2,4)-4)="return"),-RIGHT(B2,LEN(B2)-FIND(" ",B2,4)),RIGHT(B2,LEN(B2)-FIND(" ",B2,4))))

Glad it helps! Please mark the thread as 'Solved'

To mark your thread solved do the following:
- Go to the first post
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save

Hi. Try

=IF(OR(B2="below MTA",B2="below threshold"),0,IF(OR(MID(B2,FIND(" ",B2,1)+1,FIND(" ",B2,4)-4)="deliver",MID(B2,FIND(" ",B2,1)+1,FIND(" ",B2,4)-4)="return"),-RIGHT(B2,LEN(B2)-FIND(" ",B2,4)),RIGHT(B2,LEN(B2)-FIND(" ",B2,4))))

You are welcome

Shortened formula...interchanged the conditions and the signs..still works though!

=IF(OR(B2="below MTA",B2="below threshold"),0,IF(MID(B2,FIND(" ",B2,1)+1,FIND(" ",B2,4)-4)="call",RIGHT(B2,LEN(B2)-FIND(" ",B2,4)),-RIGHT(B2,LEN(B2)-FIND(" ",B2,4))))