1: Find how many rates you need to get
![count instances count instances](../../I/static/images/9366608f4c9cd5e00dc3b2d546cb32ee1f3577c2f0ee9d5684f7a2c72b057b47.png)
=(LEN(A1)-LEN(SUBSTITUTE(A1,"CAD","")))/3
Copy this formula into B1
. It compares the length of your string to the length of your string after all instances of "CAD" are removed, and divides the result by 3 to find out how many times "CAD" appears.
2: Seed the next function
![it's a 1 it's a 1](../../I/static/images/f0f51805bbb9cc290d0d8db299a7f7c486e501f213c331a68e0275bb544a536d.png)
1
Write a 1
into C1
. This isn't strictly necessary but we'll do it so that the formula we write in D1
can be copied across without modification.
3: FIND
each instance
![find Find](../../I/static/images/f2f4e105a96d69d6eb4d9c901c15acdbf1399198a0fc527e4344429b2703000c.png)
=IF($B1>COLUMN()-4,FIND("CAD",$A1,C1+1),"")
This formula copies across for as many instances of the desired string as there might be in your data. Note the hard coded column value offset that compares this cell's location to the location of the counter in step 1. If you arrange your data differently, you'll have to adjust this formula (once).
Write this formula into D1
; for this example I've copied across from D1:G1
.
4: Do the MID
![get get](../../I/static/images/034895bf41f9c218d5d85a8d843ac14059102ca7dde09420c83d756aa419b76c.png)
=IFERROR(IF(D1>0,TRIM(MID($A1,D1+4,6)),""),"")
This formula copies across for as many instances.. etc. It does require you to manually copy across as many times as you did in step 3.
Write this formula into H1
; for this example I've copied across from H1:K1
.
NOTES
- It's pretty sloppy to set something like this up with so many helper columns and this much manual effort, but your question didn't say anything about your use case or workflow so I didn't put a ton of time into thinking about ways to streamline. If all your data is in column
A
, copying this setup exactly as presented here will give you the data you need in columns H:K
, and copy down for as much data as you have in A
.
- If your actual use case is looking for a
SUM
or whatever, that part is trivial of course and in this example would go in column L
; everything from B:K
can be hidden if you like, or certainly could all be combined in a 'god formula' though the effort required to do so might not be indicated depending on your workflow limitations.
- There's some fudging in step 4; I've written it to get the 6 characters that follow the space that follows "CAD" (and then trim any spaces); this assumes the size of the output you want never exceeds 6 characters. Again, you didn't include much sample data or any description in your spec so I'm guessing a bit here as to what the parameters of possible data are going to look like; this works when the string you want follows the format dd.cc or ddd.cc exactly - omitting digits will cause problems, as will rates exceeding 999.99 CAD (for the record, difficulties like this are what people mean when they ask questions to be specific and include sample data input and output).
Will there always only be two rates to extract? could there be more? could there be only one? could there be none? Will the number's format always be the same (
XXX.XX
)? – cybernetic.nomad – 2019-03-07T17:56:25.3174I'm voting to close this question as off-topic because questions must demonstrate a reasonable amount of research & understanding of the problem being solved. Please edit to either a) clearly describe your problem and the research done so far to solve it or b) include attempted solutions plus why they didn't work. In either case, be sure your expected results are clearly presented. – Tetsujin – 2019-03-07T17:58:00.650
@cybernetic.nomad Thanks for the question. There can be more rates but the format is always the same. – Darshan Singh – 2019-03-07T18:06:39.213
@Tetsujin . I am really trying to solve this problem and i have attempted to solve it via earlier posted answers but somehow they are a bit different. I would really help if you could suggest a solution. – Darshan Singh – 2019-03-07T18:07:52.673
2You need to show your workings in your question. This is not a code-writing service. – Tetsujin – 2019-03-07T18:08:57.270
What about the currency code, will it always be CAD? The fact your question got a downvote and at least one person voted to close it means something. You should try to read this, then edit your question to clarify it, show us what it is you've tried and explain how those tries failed to meet required results
– cybernetic.nomad – 2019-03-07T18:09:34.013@cybernetic.nomad .. I am pretty new .. would really appreciate some help, the nearest question or solution is this one: https://superuser.com/questions/1129538/extract-numbers-after-a-specific-word-in-a-cell -- But even this is different and can get this to work. The Currency code would be the same always.
– Darshan Singh – 2019-03-07T18:13:39.713@Tetsujin -- Just go below and check the answer this is nothing like anything thats out there. Not sure if you still think this is supposed to be downvoted. – Darshan Singh – 2019-03-07T20:36:22.083
1Darshan Singh, I understand your point that there may not be another Q/A at SUPERUSER that requires a formula solution exactly like the one I devised below. What @Tetsujin is trying to get across isn't that you weren't able to find another Q/A like it but that you didn't demonstrate, in your question, an effort of any kind to help yourself answer the question. What did you try before coming here? The question also, and I touch on this in my answer, leaves out a lot of detail. Those are the reason for the downvotes. – Alex M – 2019-03-07T21:59:12.037
1And in point of fact, the Q/A you linked is, actually, the same kind of solution as you needed. Your additional wrinkle was needing to do the same FIND more than once on a single string, but the LEN-LEN(SUBSTITUTE()) solution to that particular problem could have been fairly straightforward to find on its own, again, had you parted out your question and shown what you had tried so far. – Alex M – 2019-03-07T22:00:58.113