I have a huge list of numbers such as 123456 that I have to change into: ,'123456'. How can I do this in word or excel?


  • 1
    What's the ultimate goal? Are you trying to create a list of strings? For instance you have these numbers in a list (backslash means next row) 123 / 2345 / 2918 / .... In the end, are you trying to put all these in a single list, '123', '2345', '2918', ...? – BruceWayne Feb 28 at 15:20

Ctrl+h does the trick. Is built in in excel and word.

In Excel you can e.g. mark only one column or one row and do then your replacement with Ctrl+h . That way you keep other columns/ rows untouched.

  • Do you mean Ctrl+H? – Scott Feb 28 at 15:21

Let's assume that you have a long column of these numbers in column A of an Excel worksheet, starting with cell A1.

In cell B1, enter =",'"&A1&"'"

Then autofill down: select cell B1, hover over the square that appears at the bottom-right corner of the cell, and double-click. (If you have any gaps in the column of numbers, you may need to drag down on the square instead.)

Now right-click on the column heading and choose Copy, then right-click again and choose Paste Values.


just use the find and replace function that is built into excel.

  • you may have to play around with the custom format option, to enable excel to have a quotation (') mark at the beginning of the number – jimbob Feb 28 at 14:58

With data like:

enter image description here

Select the cells you wish to change and run this short VBA macro:

Sub FixData()
    Dim r As Range

    For Each r In Selection
        r.Value = ",'" & r.Value & "'"
    Next r
End Sub

Which will produce:

enter image description here

Each cell gets a prefix of comma quote and a suffix of quote.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.