Excel How to remove a charachter without messing up data

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

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.

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.

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.

Try copying them and paste them in another column as Values, the apostrophe will be removed and the leading zeros will remain with them.

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 ^ .

@Rob
Try the method I suggested, it works and removes the apostrophe but retains the text format of the numbers.:)

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.

I still see an apostrophe in both cells. Screen shot below:

Lol... But I don't see it at my end.

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.

My method works :)

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.

And in neither case is it visible in or impact the contents of the cell.

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

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 below
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.

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.

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.

This worked.  Then when I created the .csv files the numbers were there without the '.

Glad it worked for you.