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!
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
when I enter a number preceded by an apostrophe it shows up as an apostrophe in the formula bar. But if I change the formatting to center formatted, the apostrophe changes to a carat in the formula bar. It doesn't stay an apostrophe. If I import a column of mixed values and tell excel to format the cells as text, the numbers have apostrophes, in the formula bar. If I center them, these turn into carats. I haven't found a way to get an apostrope to be both hidden and not change to a carat when switching to center formatting. If I have an apostrophe preceding a number in my text file, I see the apostrophe before the number and in the formula bar, I have two apostrophes, one of which I can convert to a carat by switching to center formatting.
Upvote
0
Excel Facts
Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Yes, always. I am lost without my transition navigation keys.
Upvote
0
Similar threads
- Solved
- Question
