Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Remove leading zeros
We are uploading data from another application to a .txt file and then
opening the .txt in Excel 2003. The data is employee numbers, which we are copying and pasting into our learning management system. In the LMS we need the number without the leading zeros. Formating as number does not work. Do you know a formula we could use to remove the zeros when the number of leading zeros varies? This is an example of the data: 0026278, 0067405, 0000042, 0108500, |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Remove leading zeros
Try =VALUE(A1)
best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Machel" wrote in message ... We are uploading data from another application to a .txt file and then opening the .txt in Excel 2003. The data is employee numbers, which we are copying and pasting into our learning management system. In the LMS we need the number without the leading zeros. Formating as number does not work. Do you know a formula we could use to remove the zeros when the number of leading zeros varies? This is an example of the data: 0026278, 0067405, 0000042, 0108500, |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Remove leading zeros
Thank you for your response! That did not work - I think because of the comma
at the end of each number - which we do need. "Bernard Liengme" wrote: Try =VALUE(A1) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Machel" wrote in message ... We are uploading data from another application to a .txt file and then opening the .txt in Excel 2003. The data is employee numbers, which we are copying and pasting into our learning management system. In the LMS we need the number without the leading zeros. Formating as number does not work. Do you know a formula we could use to remove the zeros when the number of leading zeros varies? This is an example of the data: 0026278, 0067405, 0000042, 0108500, |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Remove leading zeros
hi
are all the emp number the same length???? try this.... =VALUE(LEFT(A7,7)) regards FSt1 "Machel" wrote: Thank you for your response! That did not work - I think because of the comma at the end of each number - which we do need. "Bernard Liengme" wrote: Try =VALUE(A1) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Machel" wrote in message ... We are uploading data from another application to a .txt file and then opening the .txt in Excel 2003. The data is employee numbers, which we are copying and pasting into our learning management system. In the LMS we need the number without the leading zeros. Formating as number does not work. Do you know a formula we could use to remove the zeros when the number of leading zeros varies? This is an example of the data: 0026278, 0067405, 0000042, 0108500, |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Remove leading zeros
This is from a previous post...
****************************** Try this array formula** : =MID(A1,MATCH(TRUE,MID(A1,ROW(INDIRECT("1:"&LEN(A1 ))),1)<"0",0),255) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) *************************************** Note that the function is volatile so once you have removed the zeros you may want to copy and paste sepcial values to improve the calculation efficiency... -- HTH... Jim Thomlinson "Machel" wrote: We are uploading data from another application to a .txt file and then opening the .txt in Excel 2003. The data is employee numbers, which we are copying and pasting into our learning management system. In the LMS we need the number without the leading zeros. Formating as number does not work. Do you know a formula we could use to remove the zeros when the number of leading zeros varies? This is an example of the data: 0026278, 0067405, 0000042, 0108500, |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Remove leading zeros
Try =VALUE(SUBSTITUTE(A1,",",""))&","
-- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Machel" wrote in message ... Thank you for your response! That did not work - I think because of the comma at the end of each number - which we do need. "Bernard Liengme" wrote: Try =VALUE(A1) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Machel" wrote in message ... We are uploading data from another application to a .txt file and then opening the .txt in Excel 2003. The data is employee numbers, which we are copying and pasting into our learning management system. In the LMS we need the number without the leading zeros. Formating as number does not work. Do you know a formula we could use to remove the zeros when the number of leading zeros varies? This is an example of the data: 0026278, 0067405, 0000042, 0108500, |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Remove leading zeros
Another possibility...
=--LEFT(A1,LEN(A1)-1)&"," Rick "Machel" wrote in message ... Thank you for your response! That did not work - I think because of the comma at the end of each number - which we do need. "Bernard Liengme" wrote: Try =VALUE(A1) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Machel" wrote in message ... We are uploading data from another application to a .txt file and then opening the .txt in Excel 2003. The data is employee numbers, which we are copying and pasting into our learning management system. In the LMS we need the number without the leading zeros. Formating as number does not work. Do you know a formula we could use to remove the zeros when the number of leading zeros varies? This is an example of the data: 0026278, 0067405, 0000042, 0108500, |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Remove leading zeros
By the way, are **all** your entries always 7 digits followed by a comma? If
so, the formula I posted can be simplified... =--LEFT(A1,7)&"," Rick "Rick Rothstein (MVP - VB)" wrote in message ... Another possibility... =--LEFT(A1,LEN(A1)-1)&"," Rick "Machel" wrote in message ... Thank you for your response! That did not work - I think because of the comma at the end of each number - which we do need. "Bernard Liengme" wrote: Try =VALUE(A1) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Machel" wrote in message ... We are uploading data from another application to a .txt file and then opening the .txt in Excel 2003. The data is employee numbers, which we are copying and pasting into our learning management system. In the LMS we need the number without the leading zeros. Formating as number does not work. Do you know a formula we could use to remove the zeros when the number of leading zeros varies? This is an example of the data: 0026278, 0067405, 0000042, 0108500, |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Remove leading zeros
I am not stuck anymore - now I have a choice of solutions. Thanks so much!!
"Rick Rothstein (MVP - VB)" wrote: Another possibility... =--LEFT(A1,LEN(A1)-1)&"," Rick "Machel" wrote in message ... Thank you for your response! That did not work - I think because of the comma at the end of each number - which we do need. "Bernard Liengme" wrote: Try =VALUE(A1) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Machel" wrote in message ... We are uploading data from another application to a .txt file and then opening the .txt in Excel 2003. The data is employee numbers, which we are copying and pasting into our learning management system. In the LMS we need the number without the leading zeros. Formating as number does not work. Do you know a formula we could use to remove the zeros when the number of leading zeros varies? This is an example of the data: 0026278, 0067405, 0000042, 0108500, |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Remove leading zeros
Alternately, change the .txt extension to .csv and double click the file.
Excel will then open the file without the leading zeroes. -- Saruman --------------------------------------------------------------------------- All Outgoing Mail Scanned By Symantec Antivirus 10 --------------------------------------------------------------------------- "Machel" wrote in message ... We are uploading data from another application to a .txt file and then opening the .txt in Excel 2003. The data is employee numbers, which we are copying and pasting into our learning management system. In the LMS we need the number without the leading zeros. Formating as number does not work. Do you know a formula we could use to remove the zeros when the number of leading zeros varies? This is an example of the data: 0026278, 0067405, 0000042, 0108500, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Remove leading zeros from downloaded data | Excel Discussion (Misc queries) | |||
REMOVE LEADING ZEROS | Excel Worksheet Functions | |||
Remove leading zeros from column | Excel Discussion (Misc queries) | |||
How to remove leading zeros using excel 2000 | Excel Discussion (Misc queries) | |||
remove leading zeros from text strings | Excel Worksheet Functions |