Excel How to remove a charachter without messing up data

I have a column in spreadsheet.  It has a (') in front of each number '006040001
When I use the find and replace with a space it turns only some of the numbers into something that looks like a formula that looks like this 9.9502E+17 instead of the number.
I tried formatting the column as text and I also tried formatting the column using copy, paste value only.

In both cases I still get this on some of the numbers.  If I click on it the real numbers do come back  but I need this to just be a text value as I am using it for a naming convention.  The number were generated by a program I have no control over.
.
kdschoolAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rob HensonFinance AnalystCommented:
For the string to stay as text, the apostrophe has to stay otherwise it gets converted to a number.

The 9.9502E+17 is showing the number in exponential format. This means that it is an extremely large number so Excel has formatted it to fit in the column. That example would be the same as 9.9502 x 10^17 or 995020000000000000
0
ShumsExcel & VBA ExpertCommented:
Hi,

  1. Insert Extra column next to this column
  2. Use "TRIM" Function & drag down until your data
  3. Copy whole range and paste special values
Special characters will be off.
0
kdschoolAuthor Commented:
I don't have the trim function on my menu or in the options to add it.  How can i format these values so they stay as a number if I remove that '  .  I need to have them as the full number and not that format.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Rob HensonFinance AnalystCommented:
TRIM is a formula function:

=TRIM(A2)   will remove surplus spaces and some (but not all) non-printing characters from a cell.

As mentioned earlier, in order to have a number string as text eg with the leading zeroes like you have in the question, the apostrophe has to stay. Removing it will force excel to re-evaluate the cells as a number and leading zeroes will be removed.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Try copying them and paste them in another column as Values, the apostrophe will be removed and the leading zeros will remain with them.
0
Rob HensonFinance AnalystCommented:
To stay as text the apostrophe cannot be removed.

It is Excel's way of recognising the cell contents as text; for all intents and purposes it is ignored by Excel and is not seen as part of the Text string.

If you were to right align it would change to a " , if you were to centre align it changes to a ^ .
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@Rob
Try the method I suggested, it works and removes the apostrophe but retains the text format of the numbers.:)
0
Rob HensonFinance AnalystCommented:
Nope not seeing it.

Cell A2 '006040001
B2 = TRIM(A2)
C2 copied B2 and pasted as values, '006040001   still the same as A2.

If I format the cell as text, manually remove the apostrophe and press enter the apostrophe returns.

I have seen instances in the past where the apostrophe is not visible for some reason but the cell is still treated as text. Those instances where the apostrophe is visible, it has no impact on the cell contents; it is merely telling excel that the cell contains left aligned text.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@Rob
Please refer to the attached.
ApostropheRemoved.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob HensonFinance AnalystCommented:
I still see an apostrophe in both cells. Screen shot below:
Screen shot
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Lol... But I don't see it at my end.

A1.jpg
B1.png
0
Rob HensonFinance AnalystCommented:
Another way to check whether it is having an impact on the cell contents is with the LEN function.

With the above file:

=LEN(A1)   result is 9 (4 zeroes and 12456), the apostrophe is not part of the cell.
=LEN(B1)   same result.

As per screen shots, the apostrophe is only visible in the address bar, it is not visible in the cell. It can be ignored.
0
ShumsExcel & VBA ExpertCommented:
My method works :)
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
As per screen shots, the apostrophe is only visible in the address bar, it is not visible in the cell. It can be ignored.
That's what you can see in the second image. No apostrophe in the formula bar.
0
Rob HensonFinance AnalystCommented:
And in neither case is it visible in or impact the contents of the cell.
0
kdschoolAuthor Commented:
I need to convert this excel file to a .csv file.  It can't have the ' quote mark in that field.  If I turn it back to a number on the longer numbers it's putting in all zeros instead of assigning the entire number.  How can I make it leave that entire number in place instead of that formula or the number that converts the end of the number to all zerors.

original number is 750010000750010001750010002
it converts it to  7.5001000075001E+26
Then when I click back on it goes to this 750010000750010000000000000


I need that original number to stay intact it can be text or anything as long as that quote does not show up when I create the .csv file
0
Rob HensonFinance AnalystCommented:
It would seem that there are potentially system or locale differences affecting this.

I am in UK using Excel 2013

Converting to CSV will ignore the apostrophe. See screenshot belowScreen shot
The Excel screen in the background shows the number string with an apostrophe. I then saved that file as a CSV file and have opened the CSV file in Notepad in the foreground. As you can see from the highlights, the apostrophe is present in Excel but when saved to CSV it is not there as part of the string.
0
Rob HensonFinance AnalystCommented:
The reason for the conversion from

750010000750010001750010002

to

7.5001000075001E+26
 is as mentioned earlier its a large number which Excel believes is easier to display in exponential (scientific) format

Converting back to a number then converts to:

750010000750010000000000000

This is because Excel will only recognise numbers up to 15 significant places.
0
Rob HensonFinance AnalystCommented:
In answer to your question:

How can I make it leave that entire number in place

Don't do the find and replace or anything else with the number that comes from the other system. Just use the file as it comes out of the other system and save it as csv, open it in Notepad to check. Opening it in Excel will still show the (non-existent formatting purposes only) apostrophes or will convert the string to a number.
0
kdschoolAuthor Commented:
This worked.  Then when I created the .csv files the numbers were there without the '.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Glad it worked for you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.