Extract text w/ different lengths from a cell

[ORoxo]

Ok, guys. I've been pumping my head against the wall trying to get this formula working and I don't understand why it doesn't.

Let's say I have the A column full of codes likes this


123 - AAA - BBB
321 - ABC - FDPFFSFSFS
412 - DLD - DKLFDD


I need to extract the last part after the 2nd "-". It might have different lengths according to the cell so I can not simply put a number there.

I was trying something like:

=RIGHT(A1;FIND("-";A1))

I thought that might work but somehow it doesn't. Could you please help me out? I have already found formulas that work over the internet. However, they were complex and I want to keep it simple. Furthermore, now I'm really intrigued why this isn't working.


Thanks,
ORoxo

 

[gaz_chops]

If it always begins with 3 numbers followed by - , then try

=MID(A2,FIND("- ",A2,7)+2,99)

 

[ORoxo]

If it always begins with 3 numbers followed by - , then try

=MID(A2,FIND("- ",A2,7)+2,99)

I might have explained myself wrong though. The first part might have different lengths as well. It might be, for example:

A - ZZZ - 1231
BCD - LDL - 1
LPLPLPLP - XA - 7318723919873

And I'm only interested in the characters in bold. Although they're might not be numbers only.

 

[gaz_chops]

Then try

=MID(A2,FIND("§",SUBSTITUTE(A2,"-","§",2),7)+2,99)

 

[ORoxo]

Then try

=MID(A2,FIND("§",SUBSTITUTE(A2,"-","§",2),7)+2,99)

It works!!! Thank you very much

The instance on Substitute formula refers to which is the nº of the occurrence you want to substitute, right?

 

[gaz_chops]

You're welcome.

Yes, it replaces the 2nd - with §, then finds § as the start point (+2). 99 is the number of characters to return, if it is likely to be more then increase it.

Noticed a little mistake, it doesn't need the 7, use

=MID(A2,FIND("§",SUBSTITUTE(A2,"-","§",2))+2,99)

 

[ukmikeb]

Here's a few more ways to achieve your objectives :-
ORoxo

	A	B	C	D
22	123 - AAA - BBB	BBB	BBB	BBB
23	321 - ABC - FDPFFSFSFS	FDPFFSFSFS	FDPFFSFSFS	FDPFFSFSFS
24	412 - DLD - DKLFDD	DKLFDD	DKLFDD	DKLFDD

<thead>
</thead><tbody>
</tbody>

Excel 2007

Worksheet Formulas Cell Formula B22 =TRIM(RIGHT(SUBSTITUTE(A22,"-",REPT(" ",LEN(A22))),LEN(A22))) C22 =MID(SUBSTITUTE(A22,"- ","##",2),FIND("##",SUBSTITUTE(A22,"- ","##",2))+2,99) D22 =MID(A22,FIND("-",A22,FIND("-",A22)+1)+2,99) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

hth

 

[ORoxo]

You're welcome.

Yes, it replaces the 2nd - with §, then finds § as the start point (+2). 99 is the number of characters to return, if it is likely to be more then increase it.

Noticed a little mistake, it doesn't need the 7, use

=MID(A2,FIND("§",SUBSTITUTE(A2,"-","§",2))+2,99)

Here's a few more ways to achieve your objectives :-
ORoxo

	A	B	C	D
22	123 - AAA - BBB	BBB	BBB	BBB
23	321 - ABC - FDPFFSFSFS	FDPFFSFSFS	FDPFFSFSFS	FDPFFSFSFS
24	412 - DLD - DKLFDD	DKLFDD	DKLFDD	DKLFDD

<tbody>
</tbody>

Excel 2007

Worksheet Formulas Cell Formula B22 =TRIM(RIGHT(SUBSTITUTE(A22,"-",REPT(" ",LEN(A22))),LEN(A22))) C22 =MID(SUBSTITUTE(A22,"- ","##",2),FIND("##",SUBSTITUTE(A22,"- ","##",2))+2,99) D22 =MID(A22,FIND("-",A22,FIND("-",A22)+1)+2,99) <tbody> </tbody>

<tbody>
</tbody>

hth

Thanks, guys. It was really helpful