Hidden Apostrophe in cell

[ckrohn]

I have a file of product numbers that contain both letters and numbers. Some of the products have an apostrophe in front of the first digit (Ex: '71245, or 'P1278) that is only visible in the formula bar when you select the cell. The apostrophe is not visible in the cell when unselected. I want to remove the apostrophes so that I can drop the list into a database. I tried to do "Text to columns" delimited but since the apostrophe is hidden it does not recognize it. I also tried copy and pasting as value, copy and pasting into a word file, and changing the format on the cells to various forms. None of these have worked. Please help!

 

[JoeMo]

I have a file of product numbers that contain both letters and numbers. Some of the products have an apostrophe in front of the first digit (Ex: '71245, or 'P1278) that is only visible in the formula bar when you select the cell. The apostrophe is not visible in the cell when unselected. I want to remove the apostrophes so that I can drop the list into a database. I tried to do "Text to columns" delimited but since the apostrophe is hidden it does not recognize it. I also tried copy and pasting as value, copy and pasting into a word file, and changing the format on the cells to various forms. None of these have worked. Please help!

Did you try copying to another range, then paste as values?

 

[ckrohn]

Did you try copying to another range, then paste as values?

Hello, Yes the data is currently in column C and I tried to paste into Column D with no luck

 

[MARK858]

Have you tried in column D using =CLEAN(C2) then copy/paste as values (change the C2 to suit)?

 

[ckrohn]

have you tried in column d using =clean(c2) then copy/paste as values (change the c2 to suit)?

it worked!! Thank you!!!

 

[MARK858]

You're welcome

 

[higrm]

What you see as an apostrophe is actually an indicator of a left formatted string. If you would have selected a cell and chosen to right format it, the apostrophe would have changed to a double quote, center formatting is indicated with a carat symbol ^. You could also have used =Value(c2) in column D and it would have also have converted the string into a value.

 

[MARK858]

You could also have used =Value(c2) in column D and it would have also have converted the string into a value

Not if it is text like the OP's 'P1278, you get a #VALUE error. Put 'P1278 in C2 and test.

The leading apostrophe can be an import as text (or the cell formatted as text before the data was added) and then the apostrophe doesn't change if you change the alignment.

 

[higrm]

Not if it is text like the OP's 'P1278, you get a #VALUE error. Put 'P1278 in C2 and test.

Good point. I guess =iferror(value(c2),c2) would have been my fall back. I've never used the Clean() function. But if I had a column of mixed numeric and alphanumeric, I'd probably leave the numeric ones as text unless I was positive the numeric entries really represented numbers and not codes/part numbers.

If the imported field has an apostrophe wouldn't it be visible if it weren't part of the cell formatting? I'll try to import some numbers as text. I learn so much reading these replies.

Thanks for the lesson.

 

[MARK858]

If the imported field has an apostrophe wouldn't it be visible if it weren't part of the cell formatting? I'll try to import some numbers as text. I learn so much reading these replies.

It acts exactly the same as if you typed the apostrophe manually, like I stated type in 'P1278 including the apostrophe and see what happens.