newbie question

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

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


The <> "0" not equal to condition will return everthing that is not in your first expression ="0" hence fulfilling that condition so the third condition will never be reached!

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

Would eliminate the false and deal with the numbers if they were added as numbers

If however it is inconsistent and some are numbers and some are text the 2 expressions would have to be combined

Try something like
=IF(isnumber(A17),"XXX"&text(A17,"0000000000"),IF(LEFT(A17,1)="0","XXX"&A17,IF(A17 <> "",RIGHT(A17, LEN(A17)-SEARCH("XXX", A17)+1),"")))

If it is just a number then "xxx" and a ten digit number with leading zeros
Else if it is text starting with a 0 then 'xxx' and the text
Else if it the normal text string trim it to the bit starting XXX

if statements are a pain, especially just before a weekend, especially when they are nested they can become quite messy and the order of the conditions in crucial as it is actually else if

Anyway I think the above should work

If not please let me know


Regards

Dav

excyauseme@gmail.com 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!

Well we got there eventually , and thats the main thing, without seeing the spreadsheet and all eventualities it can take a while, numbers looking like text and text looking like numbers. Then when we think we have done it someone enters data another way that we have not thought of. In the future data validation from a list could be the way to go to ensure consistency of data entry, although if the data is like that when you have been given it it does not make much difference!

Thanks for the feedback

Regards

Dav