Thread: Pulling text from the right end of a string
View Single Post
  #7   Report Post  
Posted to microsoft.public.excel.programming
Ron Rosenfeld Ron Rosenfeld is offline
external usenet poster
  
Posts: 5,651
Default Pulling text from the right end of a string
On Thu, 3 Jul 2008 12:55:24 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

Give this a try...

=TRIM(MID(A1,FIND("|",SUBSTITUTE(A1,"-","|",LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))))+1,99))

Note.... it returns your number as text; if you want it to be a real number,
use this formula instead...

=--TRIM(MID(A1,FIND("|",SUBSTITUTE(A1,"-","|",LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))))+1,99))

Rick

Given the format of the data,

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))

should also work.

If you really want to use the hyphen, then:

=TRIM(RIGHT(SUBSTITUTE(A1,"-",REPT(" ",99)),99))
--ron
Reply With QuoteReply With Quote