Hello can someone help me with a formula to extract the following data from a string similar to the below: 1.42900
Crew[1.00]*W/C Labor Cost[150.99000]/(Std Prod Rate[1.42900])
I started playing with =RIGHT(A1,LEN(A1) - SEARCH("]",A2) formula but wasn't able to extract the info I was after...
Please let me know.
Thank you,
Re: =Right Search formula?
with PowerQuery
Power Query for
- Excel 2010 Pro Plus or Excel 2013 Pro Plus can be downloaded as free add-in from MS site (choose correct version). I suggest Power Pivot also
- Excel 2016 or Excel 365 is built-in, aka Get&Transform
- PowerQuery works with M-language and does not contain any classic formulae or vba code
- What is PowerQuery? see here: Getting Started with PowerQuery (Get&Transform) in Excel
How about:
Formula:
Please Login or Register to view this content.
If you have more than 3 instances of "[" prior to the number you want you may want to try this instead (provided the character "]" is always last and right before the number you're looking for):
Formula:Please Login or Register to view this content.
Last edited by ron2k_1; 04-23-2018 at 07:29 PM.
Ron
Knowledge is knowing that a tomato and pepper are fruits. Wisdom is knowing whether to put these in a fruit salad
Kindly
[1] Use code tags. Place "[code]" before the first line of code and "[/code"]" after the last line of code. Exclude quotation marks
[2] Mark your post [SOLVED] if it has been answered satisfactorily by editing your original post in advanced mode.
[3] Thank (using the little scale) those that provided useful help; its nice and its very well appreciated
If you have more than 3 instances of "[" prior to the number you want you may want to try this instead (provided the character "]" is always last and right before the number you're looking for):
Formula:Please Login or Register to view this content.
Try this
With this formula you don't have to worry how many characters in the last brackets. The formula will adjust automatically.
Formula:Please Login or Register to view this content.
v A B 1 Crew[1.00]*W/C Labor Cost[150.99000]/(Std Prod Rate[1.42900]) 1.42900
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
we can simply use the mid and search by add in more text to become an unique text. (use "rate[" instead of "[")
Formula:Please Login or Register to view this content.
The 7 mean the len of the number. (=len("1.42900"))
you need to change "rate[" (the +5 next to the search the len of "rate[", to exclude "rate[")
and the "7" if required.
or
Formula:Please Login or Register to view this content.
use same way (use "])" instead of "])
Last edited by BoredWorker; 04-24-2018 at 01:58 AM.
Hope you can learn every time you visit here.
If you still confuse on how it work, kindly ask or go to
i) Formula - Formula (Ribbon) > Formula Auditing (Section) > Evaluate Formula > Evaluate; or
ii) VBA/Code - Click F8 to see how it work step by step.
It it take care of your question, Please:
Mark tread as [Solved] [Thread Tools->Mark thread as Solved];and
Click *Add Reputation to thank anyone solved your question.
One way:
=TRIM(LEFT(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,"]","["),"[",REPT(" ",500)),1000),500))
or try
=--(SUBSTITUTE(SUBSTITUTE(RIGHT(SUBSTITUTE(A1,"[",REPT(" ",99),LEN(A1)-LEN(SUBSTITUTE(A1,"[",""))),99),"]",""),")",""))
If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Thank you all for the responses ill try these out!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks