Hi all, I have a column of formula's which give results for a search but need to simplify a formula to get it to extract the numbers within the result and multiply them all.
One of the results is:
[AWA 3x1x4x1x2]
and I need to get rid of the first part [AWA] to then get a result of the rest.
This would give me ? number then I could multiply it by an additional number.
The result is not always the same, the result could be:
[AWA 3x1x2] or [SWA 2x1x4] etc etc.
Thanks for any help.
Try:
where A1 contains your original string.Code:=PRODUCT(IF(ISNUMBER(MID(A1,ROW($A$1:OFFSET($A$1,LEN(A1)-1,0)),1)+0),MID(A1,ROW($A$1:OFFSET($A$1,LEN(A1)-1,0)),1)+0))
You must confirm this formula with CTRL+SHIFT+ENTER not just ENTER. You will see { } brackets appear around the formula.
This formula will extract and multiply out the numbers... no need to eliminate text
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Another way would be to create a named range, eg:
You can then use this reference in your formulae... so assuming A1 holds:Code:Name: _Formula RefersTo: =EVALUATE(TRIM(SUBSTITUTE(SUBSTITUTE(MID(Sheet1!$A2,FIND(" ",Sheet1!$A2)+1,1000),"]",""),"x","*"))) change references as required
Then to get the result of the aboveCode:[AWA 3x1x4x1x2]
Code:A2: =_formula will generate 24
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Thanks for your help, I tried first version to no avail but the assistance from DonkeyOte is leading me in thr right direction - can the formula be written in column B and my results in column A, so B1 adds up A1, B2 for A2 and so on? If I try at present I get a #value - im working within excel 2002.
After you entered my formula, did you hold down the CTRL+SHIFT keys and then hit ENTER?Originally Posted by sparx
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Yes I did, all I got was a 0 value
When I done as you described it placed {} around the formula but still gave me a 0 - I will try again within excel 2002 and not excel for vista.
See attached... it worked for me, seemingly.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
NVBC please accept my appologies, your version also works and works fine. Thank you, and thank you DonkeyOte for your help too.
NVBC, please can I ask for additional help, if my original result is AWA 1x4x2x3x150, how could your formula remove the first part AWA before the space and leave out the last part after the last x so the formula would only see 1x4x2x3?
Not sure if you are asking for how the formula works, or for another formula entirely that extracts just the middle part?
Please clarify.
BTW, it's NBVC
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
the formula works out cable lengths - and one section i need will show the cable type in this example AWA or SWA then the number of times each cable is run maybe 3x1x4 so is 3 sets of 1 lot of 4 or 12 cables and the last part is the size of the cable - so result is normally SWA 2x1x4x150 or AWA 3x1x4x240 - all I need is the 2x1x4 or the 3x1x4 part then I will use this result to multiply all the cable of that size found, hope this helps.
Try:
=MID(A1,FIND(" ",A1)+1,FIND("^^",SUBSTITUTE(A1,"x","^^",3))-FIND(" ",A1)-1)
this assumes you have consistant format... i.e. AxBxCxD
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
NBVC, thanks - between both your formula's I can now work out the sums I need - thanks for yout time.
Please mark your thread as Solved.
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks