Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
newbie question
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
newbie question
=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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
newbie question
=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 -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=559376 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
newbie question
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 -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=559376 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Newbie With A Question | Excel Worksheet Functions | |||
Anybody Help with previous question | Excel Discussion (Misc queries) | |||
Newbie question: Matching data/2 wkshts copying info over | Excel Worksheet Functions | |||
Hints And Tips For New Posters In The Excel Newsgroups | Excel Worksheet Functions | |||
Newbie to charts question - projecting values between data points | Excel Discussion (Misc queries) |