| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Need help with TRIM function
Hello,
I have a table with these values, some of which are INVALID. 0120120001 VALID 0000212462 INVALID 0150214785 VALID 04874A1234 VALID 0005421424 INVALID 00000A1240 INVALID 0014A27870 INVALID As you can see, from above: 1 - There are values that are only numeric. 2 - There are ALSO values that are BOTH numeric and text. 3 - You will also notice that there is a only ONE type of pattern in these numbers and that is with the numbers that begin with a 0 (zero), then have 9 numbers following, eg 04874A1234. 4 - To clarify, I have indicated which are the VALID and INVALID values, please disregard the mentioning of the actual VALID and INVALID text, it is NOT in the datasets. I need to run a TRIM function that will: a) disregard any values that are VALID, b) trim off ALL of the leading zeros EXCEPT the last zero in ANY of the INVALID's. How can this be done? BTW, since I will be importing this into Access, and Access DOES NOT like mixed datatypes, ALL of the values will need to be TEXT (or should they be GENERAL?). TIA for your replies. Phil. |
|
#2
|
|||
|
|||
|
Need help with TRIM function
=if(b2="valid",a2,trim(b2))
Then, custom format the column that contains this formula as 0000000 ************ Anne Troy www.OfficeArticles.com "Phil" wrote in message ... Hello, I have a table with these values, some of which are INVALID. 0120120001 VALID 0000212462 INVALID 0150214785 VALID 04874A1234 VALID 0005421424 INVALID 00000A1240 INVALID 0014A27870 INVALID As you can see, from above: 1 - There are values that are only numeric. 2 - There are ALSO values that are BOTH numeric and text. 3 - You will also notice that there is a only ONE type of pattern in these numbers and that is with the numbers that begin with a 0 (zero), then have 9 numbers following, eg 04874A1234. 4 - To clarify, I have indicated which are the VALID and INVALID values, please disregard the mentioning of the actual VALID and INVALID text, it is NOT in the datasets. I need to run a TRIM function that will: a) disregard any values that are VALID, b) trim off ALL of the leading zeros EXCEPT the last zero in ANY of the INVALID's. How can this be done? BTW, since I will be importing this into Access, and Access DOES NOT like mixed datatypes, ALL of the values will need to be TEXT (or should they be GENERAL?). TIA for your replies. Phil. |
|
#3
|
|||
|
|||
|
Need help with TRIM function
Assuming that you only want to have the VALID values from your table, which
contain a numeric, and you want to strip the leading zeros from the 9 digit number so that the following would be true - the (9) digit numeric 012012000 would end up as an (8) digit numeric of 12012000... You can use the following formula to produce a table that you can either filter or place into a pivot table to isolate your "VALID" entries... =IF(ISERROR(VALUE(A4)),,VALUE(A4)) What this will do is to produce a numeric entry without leading zeroes and produce a "0" entry from the list for those that are alpha/numeric... -- Thanks for your help - Joe Mac "Phil" wrote: Hello, I have a table with these values, some of which are INVALID. 0120120001 VALID 0000212462 INVALID 0150214785 VALID 04874A1234 VALID 0005421424 INVALID 00000A1240 INVALID 0014A27870 INVALID As you can see, from above: 1 - There are values that are only numeric. 2 - There are ALSO values that are BOTH numeric and text. 3 - You will also notice that there is a only ONE type of pattern in these numbers and that is with the numbers that begin with a 0 (zero), then have 9 numbers following, eg 04874A1234. 4 - To clarify, I have indicated which are the VALID and INVALID values, please disregard the mentioning of the actual VALID and INVALID text, it is NOT in the datasets. I need to run a TRIM function that will: a) disregard any values that are VALID, b) trim off ALL of the leading zeros EXCEPT the last zero in ANY of the INVALID's. How can this be done? BTW, since I will be importing this into Access, and Access DOES NOT like mixed datatypes, ALL of the values will need to be TEXT (or should they be GENERAL?). TIA for your replies. Phil. |
|
#4
|
|||
|
|||
|
Need help with TRIM function
Anne Troy wrote...
=if(b2="valid",a2,trim(b2)) Then, custom format the column that contains this formula as 0000000 .... You should know better. Number formats don't affect text values (well, the 4th part of a #;#;#;# format does, but Excel won't accept 0000000 as the 4th part of a custom number format). If the OP is seeing entries with leading zeros, then either those entries must be text, so unaffected by number formatting, or numbers with a custom number format already applied. You failed to notice the OP's statement that the VALID/INVALID indicators aren't in the dataset, but were included only to show which entries were valid and which weren't. You also failed to notice that OP's ultimate target for this data is Access, and Access does squat all with Excel custom number formats. And since when has Excel's TRIM function removed any character other than the ASCII space character? Looks like the OP wants to remove zeros. [Looks like you need to spend more time reading postings more carefully.] "Phil" wrote in message .... I have a table with these values, some of which are INVALID. 0120120001 VALID 0000212462 INVALID 0150214785 VALID 04874A1234 VALID 0005421424 INVALID 00000A1240 INVALID 0014A27870 INVALID As you can see, from above: 1 - There are values that are only numeric. 2 - There are ALSO values that are BOTH numeric and text. 3 - You will also notice that there is a only ONE type of pattern in these numbers and that is with the numbers that begin with a 0 (zero), then have 9 numbers following, eg 04874A1234. You mean 9 decimal digits and letters. Any letters or just a few? In any case, it looks like you mean a *single* leading zero followed by an alphanumeric character other than zero (or only 1-9?) followed by 8 more alphanumeric characters. 4 - To clarify, I have indicated which are the VALID and INVALID values, please disregard the mentioning of the actual VALID and INVALID text, it is NOT in the datasets. Looks like any entry beginning with 2 or more leading zeros is invalid. Is that the case? If so, invalid entries X satisfy MID(X,2,1)="0". I need to run a TRIM function that will: a) disregard any values that are VALID, b) trim off ALL of the leading zeros EXCEPT the last zero in ANY of the INVALID's. .... TRIM only removes spaces, not zeros. Using SUBSTITUTE to replace zeros with spaces then passing the result to TRIM, then using SUBSTITUTE again to convert the remaining spaces back to zeros would be dangerous because it'd delete trailing runs of zeros and truncate any inner runs of zeros. Should the invalid entries be shortened? That is, should |0000212462| become |0212462| or do they need to be padded back out to 10 characters? If they only need to be truncated, =IF(MID(A1,2,1)<"0",A1, MID(A1,MATCH(FALSE,MID(A1,{1,2,3,4,5,6,7,8,9,10},1 )="0",0)-1,10)) If they need to be padded on the right with zeros, =IF(MID(A1,2,1)<"0",A1, LEFT(MID(A1,MATCH(FALSE,MID(A1,{1,2,3,4,5,6,7,8,9, 10},1)="0",0)-1,10) &"000000000",10)) |
|
#5
|
|||
|
|||
|
Need help with TRIM function
Joe Mac wrote...
Assuming that you only want to have the VALID values from your table, which contain a numeric, and you want to strip the leading zeros from the 9 digit number so that the following would be true - the (9) digit numeric 012012000 would end up as an (8) digit numeric of 12012000... You can use the following formula to produce a table that you can either filter or place into a pivot table to isolate your "VALID" entries... =IF(ISERROR(VALUE(A4)),,VALUE(A4)) What this will do is to produce a numeric entry without leading zeroes and produce a "0" entry from the list for those that are alpha/numeric... .... You have stated your assumptions, but the OP's valid entries included 04874A1234 VALID which isn't numeric. Your formula won't leave it alone, which is one of the OP's requests. |
|
#6
|
|||
|
|||
|
Need help with TRIM function
Harlan Grove wrote...
.... .... Looks like any entry beginning with 2 or more leading zeros is invalid. Is that the case? If so, invalid entries X satisfy MID(X,2,1)="0". .... Stupid of me. The test for invalidity should be LEFT(X,1)="00". Which would make the formulas (truncated) =IF(LEFT(A1,2)<"00",A1, MID(A1,MATCH(FALSE,MID(A1,{1,2,3,4,5,6,7,8,9,10},1 )="0",0)-1,10)) (padded on the right with zeros) =IF(LEFT(A1,2)<"00",A1, LEFT(MID(A1,MATCH(FALSE,MID(A1,{1,2,3,4,5,6,7,8,9, 10},1)="0",0)-1,10) &"000000000",10)) |
|
#7
|
|||
|
|||
|
Need help with TRIM function
Hi Anne:
You may have misunderstood me. I just put the words VALID and INVALID in there to show you all which values where good vs. not good. And I want to keep any leading zeros. Thanks. "Anne Troy" wrote: =if(b2="valid",a2,trim(b2)) Then, custom format the column that contains this formula as 0000000 ************ Anne Troy www.OfficeArticles.com "Phil" wrote in message ... Hello, I have a table with these values, some of which are INVALID. 0120120001 VALID 0000212462 INVALID 0150214785 VALID 04874A1234 VALID 0005421424 INVALID 00000A1240 INVALID 0014A27870 INVALID As you can see, from above: 1 - There are values that are only numeric. 2 - There are ALSO values that are BOTH numeric and text. 3 - You will also notice that there is a only ONE type of pattern in these numbers and that is with the numbers that begin with a 0 (zero), then have 9 numbers following, eg 04874A1234. 4 - To clarify, I have indicated which are the VALID and INVALID values, please disregard the mentioning of the actual VALID and INVALID text, it is NOT in the datasets. I need to run a TRIM function that will: a) disregard any values that are VALID, b) trim off ALL of the leading zeros EXCEPT the last zero in ANY of the INVALID's. How can this be done? BTW, since I will be importing this into Access, and Access DOES NOT like mixed datatypes, ALL of the values will need to be TEXT (or should they be GENERAL?). TIA for your replies. Phil. |
|
#8
|
|||
|
|||
|
Need help with TRIM function
Hi Joe,
I don't want to remove ALL of the leading zeros. I need to keep only one. Check back to see my examples in the original post to see what I mean, but essentially, I want to ignore any of the values that are VALID, such as a 0120120001 value, but I want to go after any ones that are INVALID, such as 0000212462 , and then change it to 0212462. Does that help to explain it better? Phil. "Joe Mac" wrote: Assuming that you only want to have the VALID values from your table, which contain a numeric, and you want to strip the leading zeros from the 9 digit number so that the following would be true - the (9) digit numeric 012012000 would end up as an (8) digit numeric of 12012000... You can use the following formula to produce a table that you can either filter or place into a pivot table to isolate your "VALID" entries... =IF(ISERROR(VALUE(A4)),,VALUE(A4)) What this will do is to produce a numeric entry without leading zeroes and produce a "0" entry from the list for those that are alpha/numeric... -- Thanks for your help - Joe Mac "Phil" wrote: Hello, I have a table with these values, some of which are INVALID. 0120120001 VALID 0000212462 INVALID 0150214785 VALID 04874A1234 VALID 0005421424 INVALID 00000A1240 INVALID 0014A27870 INVALID As you can see, from above: 1 - There are values that are only numeric. 2 - There are ALSO values that are BOTH numeric and text. 3 - You will also notice that there is a only ONE type of pattern in these numbers and that is with the numbers that begin with a 0 (zero), then have 9 numbers following, eg 04874A1234. 4 - To clarify, I have indicated which are the VALID and INVALID values, please disregard the mentioning of the actual VALID and INVALID text, it is NOT in the datasets. I need to run a TRIM function that will: a) disregard any values that are VALID, b) trim off ALL of the leading zeros EXCEPT the last zero in ANY of the INVALID's. How can this be done? BTW, since I will be importing this into Access, and Access DOES NOT like mixed datatypes, ALL of the values will need to be TEXT (or should they be GENERAL?). TIA for your replies. Phil. |
|
#9
|
|||
|
|||
|
Need help with TRIM function
I should be strung up by my toes, I suppose.
************ Anne Troy www.OfficeArticles.com "Harlan Grove" wrote in message ups.com... Anne Troy wrote... =if(b2="valid",a2,trim(b2)) Then, custom format the column that contains this formula as 0000000 ... You should know better. Number formats don't affect text values (well, the 4th part of a #;#;#;# format does, but Excel won't accept 0000000 as the 4th part of a custom number format). If the OP is seeing entries with leading zeros, then either those entries must be text, so unaffected by number formatting, or numbers with a custom number format already applied. You failed to notice the OP's statement that the VALID/INVALID indicators aren't in the dataset, but were included only to show which entries were valid and which weren't. You also failed to notice that OP's ultimate target for this data is Access, and Access does squat all with Excel custom number formats. And since when has Excel's TRIM function removed any character other than the ASCII space character? Looks like the OP wants to remove zeros. [Looks like you need to spend more time reading postings more carefully.] "Phil" wrote in message ... I have a table with these values, some of which are INVALID. 0120120001 VALID 0000212462 INVALID 0150214785 VALID 04874A1234 VALID 0005421424 INVALID 00000A1240 INVALID 0014A27870 INVALID As you can see, from above: 1 - There are values that are only numeric. 2 - There are ALSO values that are BOTH numeric and text. 3 - You will also notice that there is a only ONE type of pattern in these numbers and that is with the numbers that begin with a 0 (zero), then have 9 numbers following, eg 04874A1234. You mean 9 decimal digits and letters. Any letters or just a few? In any case, it looks like you mean a *single* leading zero followed by an alphanumeric character other than zero (or only 1-9?) followed by 8 more alphanumeric characters. 4 - To clarify, I have indicated which are the VALID and INVALID values, please disregard the mentioning of the actual VALID and INVALID text, it is NOT in the datasets. Looks like any entry beginning with 2 or more leading zeros is invalid. Is that the case? If so, invalid entries X satisfy MID(X,2,1)="0". I need to run a TRIM function that will: a) disregard any values that are VALID, b) trim off ALL of the leading zeros EXCEPT the last zero in ANY of the INVALID's. ... TRIM only removes spaces, not zeros. Using SUBSTITUTE to replace zeros with spaces then passing the result to TRIM, then using SUBSTITUTE again to convert the remaining spaces back to zeros would be dangerous because it'd delete trailing runs of zeros and truncate any inner runs of zeros. Should the invalid entries be shortened? That is, should |0000212462| become |0212462| or do they need to be padded back out to 10 characters? If they only need to be truncated, =IF(MID(A1,2,1)<"0",A1, MID(A1,MATCH(FALSE,MID(A1,{1,2,3,4,5,6,7,8,9,10},1 )="0",0)-1,10)) If they need to be padded on the right with zeros, =IF(MID(A1,2,1)<"0",A1, LEFT(MID(A1,MATCH(FALSE,MID(A1,{1,2,3,4,5,6,7,8,9, 10},1)="0",0)-1,10) &"000000000",10)) |
|
#10
|
|||
|
|||
|
Need help with TRIM function
You're right, Phil. I did. I took it to mean it wasn't IN THE CELL with the
number, but of course you said "data set". I just read something different. LOL!! ************ Anne Troy www.OfficeArticles.com "Phil" wrote in message ... Hi Anne: You may have misunderstood me. I just put the words VALID and INVALID in there to show you all which values where good vs. not good. And I want to keep any leading zeros. Thanks. "Anne Troy" wrote: =if(b2="valid",a2,trim(b2)) Then, custom format the column that contains this formula as 0000000 ************ Anne Troy www.OfficeArticles.com "Phil" wrote in message ... Hello, I have a table with these values, some of which are INVALID. 0120120001 VALID 0000212462 INVALID 0150214785 VALID 04874A1234 VALID 0005421424 INVALID 00000A1240 INVALID 0014A27870 INVALID As you can see, from above: 1 - There are values that are only numeric. 2 - There are ALSO values that are BOTH numeric and text. 3 - You will also notice that there is a only ONE type of pattern in these numbers and that is with the numbers that begin with a 0 (zero), then have 9 numbers following, eg 04874A1234. 4 - To clarify, I have indicated which are the VALID and INVALID values, please disregard the mentioning of the actual VALID and INVALID text, it is NOT in the datasets. I need to run a TRIM function that will: a) disregard any values that are VALID, b) trim off ALL of the leading zeros EXCEPT the last zero in ANY of the INVALID's. How can this be done? BTW, since I will be importing this into Access, and Access DOES NOT like mixed datatypes, ALL of the values will need to be TEXT (or should they be GENERAL?). TIA for your replies. Phil. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Date & Time | New Users to Excel | |||
| Hyperlinks using R[1]C[1] and offset function in its cell referenc | Excel Worksheet Functions | |||
| Conversion | Excel Worksheet Functions | |||
| HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions | |||
| Find a Function to use accross different worksheets | Excel Worksheet Functions | |||
Linear Mode
