How to add ' before and after number

Question

I am new to Excel I have a around 3000 purchase numbers and I need add ' before and after the numbers, and I need a , after each row's closing '.

Eg:

'18-0000319-000',
'44-000000123-000',
'55-00000456-010',

Don't know how to do in Excel please help me on this.

Thanks in Advance

score 4 · Accepted Answer · answered Aug 02 '19 at 14:28

4

Assuming you have data in C3, here is the formula that will do the trick:

="'"&C3&"',"

and you may then drag it down.

answered Aug 02 '19 at 14:28

Rishi Mehta

399
3
10

score 1 · Answer 2 · answered Aug 02 '19 at 15:16

1

One of two/three (depending on version) ways to achieve this. Assuming your purchase numbers are in A2 down:

="'"&A2&"',"

=CONCAT("'",A2,"',") Newer versions of Excel uses this as well

=CONCATENATE("'",A2,"',") This is the older version of excel's Concatenate formula

answered Aug 02 '19 at 15:16

Mark S.

1,474
1
6
20

How to check duplicate value in Excel for eg in one column – Maxtech Aug 02 '19 at 15:19
Highlight the data range, go to conditional formatting, select `Highlight Cell Rules` and select `Duplicate Values` – Mark S. Aug 02 '19 at 17:24

How to add ' before and after number

2 Answers2