How do remove ' at the star of a numbered cell

[daza34]

Hi,

I have a ' at the front of every phone number i have on and excel sheet Example ('02278572743).

I have tried replacing it, number format, paste special but nothing is working

Any help would be appreciated

 

[Joe4]

Custom Number Formats only work on data entered as numbers, not data entered as Text.
The ' forces it to be text (which allows you to keep the leading zero, as leading zeroes have no meaning to numbers and are automatically dropped)/

You should be able to convert your text to numbers by selecting the column, going to Text to Columns (found on the Data menu), and clicking Finish.

 

[daza34]

I dont want to keep the leading zeros, i have, i have tried what you suggested but it has not worked

 

[denzo36]

Try multiplying each value in your phone number list by 1 which should convert it to a number and drop the leading zero

 

[Joe4]

That should drop the leading zeroes.

Try these exact steps:
1. Select the column
2. Change the format of the column to General
3. Go to the Data menu and select "Text to Columns"
4. Click "Finish"

If that still does not work, confirm that there are no spaces or extra special text characters in your entries.

 

[daza34]

Hi,

I have tried you suggestions but still getting '0 at the start of the phone numbers Even after using the data tab to remove the first number. ' is still at the start of what is remaining.

 

[Kerryx]

Put an apostrophe into the Other option then click on Finish

 

[daza34]

I have tried that, seams to be a format issue

 

[Alex Blakenburg]

When you tried @Joe4's method did you definitely change the formatting of the column to General BEFORE you did Date > Text to Columns ?
If it still doesn't work can you check if you have any conditional formatting on that column ?
Also do you have any worksheet events macros running ?

 

[Joe4]

If you are truly having issues with that ' at the front, the suggestion that Kerryx posted should take care of that.

If you are still having issues, please post a small sample of your data so we can see exactly what you are working with (maybe there is some important detail you are leaving out).

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.