wrote:
Thank you Dav. You are so wonderful! It worked, except when the field
is blank it still inserts SSR00000 the way I put it in, anyway. I
added a bit to your formula to handle entries like 12345 (they were
showing as #VALUE! because there was no "0" and they weren't blank -
some joker forgot the leading zeros and I can't add leading zeroes for
only 5 digit numbers, can I? I don't know... So I thought if I could
change the amended formula below to check for cases where it was a
numeric number and also not equal to zeroes, then add the
SSR00000&B132. But I couldn't get it to work. Can you do something
like =IF(LEFT(B132,1)="0","SSR"&B132,IF(LEFT(B132,1)<" 0" or ""?

Here's what it would accept...but it added SSR00000 to all the 12345's
and also the blanks (which showed as FALSE).


=IF(LEFT(B132,1)="0","SSR"&B132,IF(LEFT(B132,1)<" 0","SSR00000"&B132,IF(B132
< "",RIGHT(B132,LEN(B132)-SEARCH("SSR", B132)+1))))

Sorry to be such a moron, but my head hurts. And did you ever know
that you're my hero?


Dav wrote:
=IF(A15 < "",(RIGHT(A15, LEN(A15)-SEARCH("XXX", A15)+1)))

would work if the xxx string exists however would fail for the number
(I assume formated as text

Try as a start

=IF(LEFT(A17,1)="0","XXX"&A17,IF(A17 < "",RIGHT(A17,
LEN(A17)-SEARCH("XXX", A17)+1)))

but if the 00000012345 is a number It will need to be modified to

=IF(isnumber(A17),"XXX"&text(A17,"0000000000"),IF( A17 < "",RIGHT(A17,
LEN(A17)-SEARCH("XXX", A17)+1)))


Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=559376

Dav, you are the bomb! This worked like a charm. Thank you so much -
it totally made my day!