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!!

Try

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


Mike

"aimee209" wrote:

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!!

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" wrote in message
...
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!!

Thanks!! Both formulas worked out great!!


"aimee209" wrote:

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!!

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)" 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


"aimee209" wrote in message
...
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!!

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


"Mike H" wrote in message
...
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)" 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


"aimee209" wrote in message
...
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!!

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