Building UPC in Excel - issue is keeping leading zeros and eliminating spaces between the numbers

[stephanie07]

am building UPC codes using data imported into Excel. When copying this data over to a new column to create the UPC codes, I need to keep the leading 0's but eliminate the spaces between the columns. For example, this 0000 007 1600 to 00000071600.

I have tried changing the column to text for the 0's (which seems to work) and using Find/Replace to find the spaces and eliminate them (which doesn't work).

Per someone's recommendation, I opened the Excel file in notes, copied it into a new excel sheet, and used the following formula: =SUBSTITUTE(RIGHT(A2,LEN(A2)-SEARCH("#",SUBSTITUTE(A2,",","#",LEN(A2)-LEN(SUBSTITUTE(A2,",","")))))," ","")

Per this last step, when I save the EXCEL file and reopen it, the formula doesn't save and I lose my leading zeros.

Any suggestions on another way to do this, please? Thank you for your time.

---

I'm using Microsoft Office Home and Student 2019

[#2]

@stephanie07 

Your formula generated a #VALUE error for me.  If ou have a text string,

0000 007 1600

then 

= SUBSTITUTE(text, " ", "")

will remove the spaces and leave the remaining charactes, including leading zeros.  If, on the other hand, Excel has already been converted the string to a number

71600

then converting it to text with a number format

= TEXT(number, REPT("0",11))

should work.  I have no idea why formulas should be lost when you close a workbook.

[#3]

@stephanie07 With regard to loosing all formulae upon saving I suspect that you open the TXT file created by Notes, enter your formulae and save it without changing the file type to an Excel Workbook (i.e. XLSX). Then you save the workbook back to a flat text file, i.e. without the formulae. Just the result of these formulae are save as text.

[#4]

Good thought, that would do it.