2

I have a column in spreadsheet set to 'text' format that looks like this: xxxxxx057483245

I am attemtping to replace the x's with another series of numbers. 42424242 for example.

I go to find and replace, and enter in the 6 x's to be replaced by the new numbers. The result I get in the replaced column is 4242424057483240. Note the 5 has been replaced with a 0.

I have attempted to play around with the column formatting trying to use text, general and number with 0 decimal places with no luck. Any ideas? I know how to get around it, but thought I should try and seek the answer for posterity.

  • As a FYI, my workaround was to save the XLS file as a CSV and do the F&R in a standard text editor. Pretty easy. – Taylor Ackley Jul 6 '15 at 23:23
2

Just place an apostrophe (single quote) in front of the replacement digits.

Before:

enter image description here

and after:

enter image description here

|improve this answer|||||
  • I'll give that a try, but out of curiosity, what is the reasoning behind it? – Taylor Ackley Jul 6 '15 at 20:48
  • 2
    @TaylorAckley The single quote tells Excel to treat the value as Text and avoids the 15-digit limit. – Gary's Student Jul 6 '15 at 20:52

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.