0

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
1

If you actually want to replace the letters with a blank, use the following formula to start a "helper column" and then copy it & "Paste Values Only". Formula assumes your data starts at A2:

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

If all you want to do is make the number fully display, instead of using the "E" notation, you need to format the cells as a number. Use this for your helper column, copy it and "Paste Values Only", then format the cells as numbers:

=LEFT(A2,15)

enter image description here

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.