How to use IF to partially merge two strings

[GIS2013]

Hi, my cells contain data like this: I-III 2010, and I'd like it to be Q1 2010,

So here is my formula: IF(LEFT(C2,2)="IV", "Q2 "&RIGHT(C2, 4), "Q1 "&RIGHT(C2, 4)).

It works well for Q1 and Q2. My question is: How to deal with VII-IX 2010 and X-XII 2010?

I guess I need to use nested-IF's..

[oeldere]

1) option
Make a table of your data and use VLookup to get the value.

After that you combine the values.

2) option

use CTRL + H for that (find and replace).

If you have to do this job often, I would choose option 1.

[GIS2013]

@oeldere, take for example A1 (=2010) and B1(=I-III): what would VLOOKUP do? note I've got more years 2011-2013..

*Again, a nested-if should solve it..

[XOR LX]

Hi,

Just to confirm: are your desired results for VII-IX 2010 and X-XII 2010 Q3 2010 and Q4 2010 respectively?

Regards

[oeldere]

Probably lik this (with VLookup).

See the attached file.

[GIS2013]

@Hello XOR. Yes, correct

[GIS2013]

@oeldere, got you. thanks for the solution..

[oeldere]

Thanks for the reply.

Glad I could help.

[GIS2013]

@XOR - if your solution contains if's, I'd still want to see it, if that's ok

[XOR LX]

No, it wouldn't. A solution containing IF statements would be unfeasible since it would require as many clauses as there are potential variables, e.g.:

=IF(LEFT(C1,FIND("-",C1)-1)="I","Return This",IF(LEFT(C1,FIND("-",C1)-1)="IV","Return This",IF(LEFT(C1,FIND("-",C1)-1)="VII","Return This",...

, etc., etc.

Which is precisely the sort of limitation which a simple table and a VLOOKUP, as given by oeldere, can overcome.

If you want, and just for fun, this array formula will give you the results without a table, though I would hardly recommend such a solution!

="Q"&(2+MATCH(LEFT(C1,FIND("-",C1)-1),ROMAN(ROW($1:$1000)),0))/3&" 2010"

Regards

[GIS2013]

Many thanks XOR ... Very interesting, the feasibility limitation. I'll study later the ARRAY formula. For the while, I'm using the table+VLOOKUP option.

*Maybe it'll help somebody: Note that after you (as thread opener) mark the thread as solved, you won't receive instant emails even if new posts are being added to it.

Here is the reason for that: "There may also be other replies, but you will not receive any more notifications until you visit the forum again. All the best, Excel Help Forum"

[Rambo4711]

This is a solution without a VLOOKUP table:

=LOOKUP(B1,{"I-III","IV-VI","VII-IX","X-XII"},{"Q1","Q2","Q3","Q4"})&" "&A1

[GIS2013]

@Rambo - thanks. Could you explain how it works? LOOKUP searches for B1 in the vector result?

Please Login or Register  to view this content.

[GIS2013]

@Rambo - thanks. Could you explain how it works? LOOKUP searches for B1 in the vector result?

Please Login or Register  to view this content.

[Rambo4711]

@Rambo - thanks. Could you explain how it works? LOOKUP searches for B1 in the vector result?

Please Login or Register  to view this content.

Hi, it searches the value in B1 from the first lookup-vector. The match position in that vector will display the answer from the second answer-vector from the same position as in the first lookup-vector.

[GIS2013]

Great. Obviously these two arrays must have identical size, right?

[Rambo4711]

Great. Obviously these two arrays must have identical size, right?

Yes, so it's not so useful if you have many lookup possibilites. Then it's better with a VLOOKUP table

[XOR LX]

"This is a solution without a VLOOKUP table:

=LOOKUP(B1,{"I-III","IV-VI","VII-IX","X-XII"},{"Q1","Q2","Q3","Q4"})&" "&A1"

Yes, but again, this has the same limitations as the potential IF() solution I mentioned - what would the result be for an entry containing "XIII-XV"? Or "XVI-XVIII"?

Granted, if these are not possibilities (so that there are in fact only the four groups as originally given by the OP), then this is not an issue, but if they are then you would constantly be required to add elements to the array.

Regards

[GIS2013]

Yes, of course. Well it's all about these 4: i-iii, iv-vi, vii-ix, x-xii. So Without loss of generality..

[XOR LX]

Ah, well if you're only concerned with those four then that's fine!

Regards