Menu
How can you add an apostrophe in every field in an Excel spreadsheet without individually typing it in? I have got like 5k fields
202
Leading apostrophes force excel to treat the cell's contents as a text value. Even if the cell contains a number or date, Excel will treat it as text. The apostrophe can only be seen in the Formula bar when selecting the cell, and otherwise stays invisible.
The apostrophe ' is a special character for Excel when it appears as the first character in a cell. It tells Excel to treat the rest of the string as text.
In Microsoft Excel, you can enter the same data or text into multiple cells at once using the below simple steps. Highlight all the cells that you want to have the same text. Type the text you want. After typing the text, instead of pressing Enter , press Ctrl + Enter .
I'm going to suggest the non-obvious. There is a fantastic (and often under-used) tool called the Immediate Window in Visual Basic Editor. Basically, you can write out commands in VBA and execute them on the spot, sort of like command prompt. It's perfect for cases like this.
Press ALT+F11 to open VBE, then Control+G to open the Immediate Window. Type the following and hit enter:
for each v in range("K2:K5000") : v.value = "'" & v.value : next
And boom! You are all done. No need to create a macro, declare variables, no need to drag and copy, etc. Close the window and get back to work. The only downfall is to undo it, you need to do it via code since VBA will destroy your undo stack (but that's simple).
138
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
