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

Leske

Active Member
Joined
Aug 26, 2008
Messages
297
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
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,110
Office Version
  1. 365
Platform
  1. Windows
Try:

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

István Hirsch

Well-known Member
Joined
May 16, 2013
Messages
1,634
Or go to Edit/Replace and replace *| with nothing (to keep the original column, do this on a copy).
 

Leske

Active Member
Joined
Aug 26, 2008
Messages
297

ADVERTISEMENT

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

i changed the , to ;
 

Leske

Active Member
Joined
Aug 26, 2008
Messages
297

ADVERTISEMENT

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Active Member
Joined
Aug 26, 2008
Messages
297
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,041
Messages
5,526,416
Members
409,701
Latest member
nitmani

This Week's Hot Topics

Top