Excel formula or otherwise VBA: = right (find last |) give result

[Leske]

Hello,


I have several cells in a column where there could be 1, 2 or 3 "|" in the cell:

1093270| 29.03.2012|Mail opvragen Doc's


I just want him to look at the last "|" and give me everything that comes after the last |


i have at the moment this: (added 3 columns)

=MID(A1;FIND("|";A1)+1;150) </SPAN>

=IFERROR(MID(B1;FIND("|";B1)+1;150);B1)</SPAN>

=IFERROR(MID(C1;FIND("|";C1)+1;150);C1)</SPAN>

<TBODY>
</TBODY>

(result is: Mail opvragen Doc's)

it does the trick, but i prefer one formula so i don't have to add them.
Does anyone know a better way then this?

Kind regards,

Les

 

[steve the fish]

Try:

=MID(SUBSTITUTE(A1,"|","@",LEN(A1)-LEN(SUBSTITUTE(A1,"|","" ))),FIND("@",SUBSTITUTE(A1,"|","@",LEN(A1)-LEN(SUBSTITUTE(A1,"|",""))))+1,255)

 

[steve the fish]

Replacing the commas , for semicolon ;

 

[István Hirsch]

Or go to Edit/Replace and replace *| with nothing (to keep the original column, do this on a copy).

 

[Leske]

i got #value, could it be that it has to do with "@" that he doesn't recognize it?

i changed the , to ;

 

[Andrew Poulsom]

Does this work for you?

=TRIM(RIGHT(SUBSTITUTE(A1;"|";REPT(" ";99));99))

 

[Leske]

yes fine thx it works

but how does it work?

because rept i don't know and why do you use " ";99

Many kind regards,

Lesly

 

[Andrew Poulsom]

The REPT function repeats the text in its first argument (in this case a space) the number of times in its second argument. The 99 is just a large number. So SUBSTITUTE replaces each | in the text with 99 spaces. Then the RIGHT function extracts the rightmost 99 characters in the amended text, including some of the 99 spaces where the last | was. Finally, TRIM removes the unwanted leading spaces.

 

[Leske]

yes i found out how it worked just know.

It is easy but we always try to make it to complex to finding a sollution.

Thx very mush

also thx for the explenation