# 15 digit number with a letter on the end

I get spreadsheets with 1,000 15 digit numbers with a letter added on the end. I can retain the number by formatting the cells to Text and then remove the letter manually and it's fine. BUT if I use the Find/Replace mode to remove the letter, it converts the 15 digit number to that exponential format (is that what its called?).

Anyone know how I can efficiently replace this letter with 'blank' and retain the 15 digit number?

e.g.

123456789012345F

-> I want it to

123456789012345

Thank you!

• Do you need the number to be numeric (do calculations with it), or just preserve all 15 digits (can be text)? – fixer1234 Feb 10 '15 at 7:10
• Just need to preserve the 15 digits... will then do a vlookup to bring over another piece of data from another spreadsheet. – Carl Feb 10 '15 at 7:25
• Couldn't duplicate the problem in LibreOffice Calc; just had a chance to replicate it in Excel. The problem is just formatting. When you highlight the cells for find/replace, format them to a number with zero decimal places after the replace-all. – fixer1234 Feb 11 '15 at 2:54
• If you only want to remove the letter for the purposes of lookup, then just use `LEFT(A2,15)` directly in the lookup formula. Side note: `INDEX`+`MATCH` is more robust and faster than `VLOOKUP` – Engineer Toast Feb 18 '15 at 13:11

``````=CONCATENATE(LEFT(A2,15)," ")
``````=LEFT(A2,15)