newbie question

[[email protected]]

Hi guys. I was hoping to have some advice on how to sort out some
values in an excel column,
where the field values are entered differently. I have values like:
ABC_XYZ_XXX0000012345
ABC_XXX0000012345
XXX0000012345
0000012345 and also blank ones.

I need to standardize em all to XXX0000012345.

I've tried formulas like
=IF(B1 <> "",(RIGHT(B1, SEARCH("_XXX", B1)-1)))

but I totally don't know what I'm doing. I know it's pretty pitiful.
I did the IF because of the blanks causing #VALUE! errors.
I'll also have to do another one to add XXX to the numeric ones.

Can anyone offer some ideas on the best ways to filter out this kind of
crud
with formulas or macros? Eventually I need to use this spreadsheet
with these crazy values
and compare it versus another spreadsheet with XXX0000012345 values
entered consistently.
I was going to import them into Access and create a form/report for the
auditors here.

If anybody could help me out that would be so great! I'm a
mainframe/unix programmer now
doing access/vb on the fly. It's been a while since I had to use
msoffice docs and import them into access.

[Dav]

=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

[[email protected]]

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]

=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

[[email protected]]

[email protected] 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!

[Dav]

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