Pulling text from the right end of a string

[aimee209]

I'm trying to pull text from the right end of strings. The characters can
very in number (anywhere between 2 and 6 characters long) which makes the
=right() difficult to use.

I found a formula in another post, but it doesn't work for all and was
wondering if there is a better formula to use
=MID(C103,FIND("-",C103,20)+1,255)

String 1:
Office One - Legislative Program - 41502

String 2:
Info - IBM - 204

For String 1, the correct value of 41502 is returned. But with String 2, I
get the #VALUE! error. Please help!

Thank you!!

 

[Mike H]

Try

=MID(C103,FIND(CHAR(1),SUBSTITUTE(C103,"
",CHAR(1),LEN(C103)-LEN(SUBSTITUTE(C103," ",""))))+1,255)


Mike

 

[Rick Rothstein \(MVP - VB\)]

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

 

[aimee209]

Thanks!! Both formulas worked out great!!

 

[Mike H]

Hi Rick,

Just curious but do you see any advantage in using your second formula to
return a number compared to simply multiplying your first by 1 or even
adding 0 to your first formula?

I would prefer either of the latter to the double unary.

Mike

 

[Rick Rothstein \(MVP - VB\)]

Personally, I like the double unary... it just seems natural to me; however,
I doubt there is any measurable difference between it and the other methods
you mentioned.

Rick

 

[Ron Rosenfeld]

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