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.
.
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.
.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
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,
- Insert Extra column next to this column
- Use "TRIM" Function & drag down until your data
- Copy whole range and paste special values
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.
=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 ^ .
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.:)
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.
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.
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.
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.
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.
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 75001000075001000175001000
it converts it to 7.5001000075001E+26
Then when I click back on it goes to this 75001000075001000000000000
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
original number is 75001000075001000175001000
it converts it to 7.5001000075001E+26
Then when I click back on it goes to this 75001000075001000000000000
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.
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
75001000075001000175001000
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:
75001000075001000000000000
This is because Excel will only recognise numbers up to 15 significant places.
75001000075001000175001000
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:
75001000075001000000000000
This is because Excel will only recognise numbers up to 15 significant places.
In answer to your question:
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.
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial