10

How can you add an apostrophe in every field in an Excel spreadsheet without individually typing it in? I have got like 5k fields

enter image description here

Andhi Irawan
  • 456
  • 8
  • 15
Mr A
  • 6,448
  • 25
  • 83
  • 137

4 Answers4

31

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).

Andhi Irawan
  • 456
  • 8
  • 15
Gaijinhunter
  • 14,587
  • 4
  • 51
  • 57
  • 2
    Most people are unaware that you can use : to run full blown loops and code (code in the immediate window is restricted to one line per action, which is where the misassumption comes from). – Gaijinhunter Nov 18 '11 at 16:14
  • I just learned this. StackOverflow is awesome. Even after 10 years, this is so relatable, usable and handy. – jainashish Aug 11 '21 at 12:42
16

The way I'd do this is:

  • In Cell L2, enter the formula ="'"&K2
  • Use the fill handle or Ctrl+D to fill it down to the length of Column K's values.
  • Select the whole of Column L's values and copy them to the clipboard
  • Select the same range in Column K, right-click to select 'Paste Special' and choose 'Values'
Widor
  • 13,003
  • 7
  • 42
  • 64
2

i use concantenate. works for me.

  1. fill j2-j14 with '(appostrophe)
  2. enter L2 with formula =concantenate(j2,k2)
  3. copy L2 to L3-L14
user5917579
  • 21
  • 1
1

More universal can be: for each v Selection : v.value = "'" & v.value : next and selecting range of cells before execution

guest
  • 11
  • 1