Text Extraction - Returning specific Values
Our take
Hi,
I'm trying to put together a cost estimating spreadsheet and just playing around with ideas at the moment.
Are there any formulas i can use to extract and return specific instances of text where there might be multiple things i want to know?
Lets say I have a ledger where i keep all of my fruit sold at my fruit shop and looks like this;
Johnny bought 5x apples granny smith 3x oranges 3x strawberry
Lisa bought 1x pineapple Large fresh 2x blueberry packaged 1x grape bunch large
Kerry bought 9 x apple loose 1 x Banana bunch 5x kiwi
Is there a formula i can use that would Detect and return to me just the Quantities and type of fruit? The only reason i need this is because i cannot change the way things are entered into the fictional purchase ledger.
I hope this post makes sense!
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Blanket Formula for revenue estimatingHi, I'm trying to find a way to estimate revenue, I posted earlier today about a Hypothetical fruit shop so i'm going to continue with that. Earlier someone helped me an amazing formula to read and return Values from a purchase ledger. This worked and was able to return my values, but now I cant figure out how to get it all to calculate the actual $ amount. For my earlier example i used Johnny bought 5x apples granny smith 3x oranges 3x strawberry Lisa bought 1x pineapple Large fresh 2x blueberry packaged 1x grape bunch large Kerry bought 9 x apple loose 1 x Banana bunch 5x kiwi I now have a sheet where i can copy and paste my purchase ledger and it breaks it down https://preview.redd.it/29afs6w32dvg1.png?width=145&format=png&auto=webp&s=a9949aba4b23dbac4f83c6bb982e4bdf6dfff5aa What i can't figure out is how to get it all to calculate without having to do loads and loads of tedious work. I need it to read Apples, then than the quantity and give me the result. So lets say an Apple is 0.50c then i want it to Find Apples, find the value and times by 3 to give me $1.50. Hopefully this makes sense as its quite long winded submitted by /u/Intelligent_Let7166 [link] [comments]
- Return value based on word found in string of text in a cell?I am building a spreadsheet to help budget some expenses and am trying to automate as much as I can. I have a table in Column H with a keyword like "Gas" or "Electric" and then a corresponding category in Column I that groups this into something like "utilities". I have a spreadsheet where I export my bank statement and Column B is the vendor and Column C is the amt. I have Column D trying to group all the expenses based on the helper table in Columns H and I to quick and easy organize all the transactions for a month. I made the formula: =IFERROR(XLOOKUP(B3,ISNUMBER(SEARCH($H$3:$H$35,B2)),I:I),"Uncategorized") to try and get a return and it doesn't work just yet. I feel like I'm close but I'm not as familiar with XLOOKUP as I want and I cant figure out what I could use to make return any easier. Is there a simpler way to get a return like this from a string of text or am I missing something with how the XLOOKUP works? submitted by /u/Pmme10doller_psncard [link] [comments]