microsoft excelworksheet-function
I have many cells in an Excel sheet, using 9 characters of 0-9 and A-Z, that have some number of prefixed zeros:
000000123 000001DA2 0000009Q5 0000L210A 0000014A0 0000A5500 00K002200
I'd like to remove the leading zeros so that the values become:
123 1DA2 9Q5 L210A 14A0 A5500 K002200
How would I do this in an Excel formula? I'd prefer to avoid use of a VBA macro.
When you open the csv, you'll have the option to specify the delimiter and data type for each column. The text import wizard has 3 steps. Note that my screen shots are from Excel 2010, but this will work in exactly the same manner in Excel 2003.
Step 1: select delimited (instead of fixed width)
Step 2: select comma as your delimiter
Step 3: select each column and change the data format to "text". (You will see the word Text displayed above each column as in the screen shot below.)
The leading zeros will no longer be removed:
UPDATE: If you don't want to navigate through the process to get Excel to cooperate, you can use CSVEd, an editor designed specifically for editing delimited text files. A good text editor like Notepad++ might also work, but you won't be able to see your data arranged in columns.
I just tested it and it's a lot easier than I thought to get output like the 2nd case you specified.
Do the following:
Show Formulas button (via Formulas > Formula Auditing in Excel 2007)CSV(MS-DOS)I'll play around with it for a bit more to see if I can get it to pare down the formulas like you mentioned in case 3.
Best Answer
Here's a solution that's cell-intensive but correct.
Put your data in column A.
In B1, put the formula:
This checks for a single leading zero and strips it out.
Copy this formula to the right as many columns as there can be characters in your data (9, in this case, so you'll be going out to column J). Copy it down for each row of data.
The last column contains your data, stripped of leading zeros.