Avatar of kdschool
kdschool
 asked on

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.
.
Microsoft OfficeMicrosoft ExcelMicrosoft ApplicationsSpreadsheets

Avatar of undefined
Last Comment
Subodh Tiwari (Neeraj)

8/22/2022 - Mon
Rob Henson

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
Shums Faruk

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

ASKER
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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Rob Henson

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.
Subodh Tiwari (Neeraj)

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

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 ^ .
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Subodh Tiwari (Neeraj)

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

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.
ASKER CERTIFIED SOLUTION
Subodh Tiwari (Neeraj)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Rob Henson

I still see an apostrophe in both cells. Screen shot below:
Screen shot
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Subodh Tiwari (Neeraj)

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

A1.jpg
B1.png
Rob Henson

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.
Shums Faruk

My method works :)
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Subodh Tiwari (Neeraj)

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.
Rob Henson

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

ASKER
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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Rob Henson

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.
Rob Henson

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.
Rob Henson

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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
kdschool

ASKER
This worked.  Then when I created the .csv files the numbers were there without the '.
Subodh Tiwari (Neeraj)

Glad it worked for you.