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.
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.
.
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
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.
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.
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
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.
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.
-
Microsoft ApplicationsCertification: MCP MCSA Microsoft Certified Partner & Solutions Ass… 377 lessons
-
Microsoft ApplicationsCertification: MCSA MCSE Microsoft Certified Solutions Associate & … 398 lessons
-
Microsoft ApplicationsCertification: MCSE Microsoft Certified Systems Engineer - Identity … 440 lessons
-
Microsoft ApplicationsCertification: MCSE Microsoft Certified Systems Engineer - Securing… 324 lessons
-
Microsoft ApplicationsCertification: MOS Microsoft Office Specialist - Access 2007 Int… 207 lessons
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
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