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. |
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 |