# Extract text w/ different lengths from a cell

#### ORoxo

##### Board Regular
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:

Code:
``=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

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
If it always begins with 3 numbers followed by - , then try

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

Last edited:
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.

Last edited:
Then try

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

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?

Last edited:
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)

Last edited:
Here's a few more ways to achieve your objectives :-
ORoxo
ABCD
22123 - AAA - BBBBBBBBBBBB
23321 - ABC - FDPFFSFSFSFDPFFSFSFSFDPFFSFSFSFDPFFSFSFS
24412 - DLD - DKLFDDDKLFDDDKLFDDDKLFDD

</tbody>
Excel 2007

Worksheet Formulas
CellFormula
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>

hth

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
ABCD
22123 - AAA - BBBBBBBBBBBB
23321 - ABC - FDPFFSFSFSFDPFFSFSFSFDPFFSFSFSFDPFFSFSFS
24412 - DLD - DKLFDDDKLFDDDKLFDDDKLFDD

<tbody>
</tbody>
Excel 2007

Worksheet Formulas
CellFormula
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

Replies
2
Views
743
Replies
7
Views
683
Replies
4
Views
5K
Replies
4
Views
966
Replies
1
Views
495

1,203,029
Messages
6,053,125
Members
444,640
Latest member
Dramonzo

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back