Remove single quotes around string in excel

Question

In my excel I have the following text in a cell:

INSERT INTO `educations`(`education_id`, `school`, `specialization`, `date_from`, `date_to`, `user_id`) VALUES (NULL,'NULL','software','2006-01-01 00:00:00','2006-01-01 00:00:00',@inserted_id);

I want to (programmatically) substitute all the 'NULL' and change them to NULL (without the quotes).

I tried using the substitute function but I cannot get it working.

=SUBSTITUTE(DU7;"''NULL''";"NULL")

score 2 · Accepted Answer · edited Apr 08 '17 at 16:51

2

Try this,

=SUBSTITUTE(DU7;"'NULL'";"NULL")

Not sure why you were repeating the single quote marks.

Malcolm

edited Apr 08 '17 at 16:51

Gowtham Shiva

3,802
2
11
27

answered Apr 08 '17 at 10:35

Mal B

84
3

I can't believe that actually did it.. I feel so stupid now! Thank you! – Dennis Apr 08 '17 at 10:39
Use formula option when you post a formula – Gowtham Shiva Apr 08 '17 at 10:41

Gowtham Shiva · Answer 2 · 2017-04-08T10:47:41.757

1

Please try this,

Single quote -

=SUBSTITUTE(DU7;"'NULL'";"NULL")

Substituting single quotes is easier, just like how you substitute other strings, But substituting double quotes has to be written similar to how you have did.

Double Quote-

=SUBSTITUTE(DU7;"""NULL""";"NULL")

edited Apr 08 '17 at 10:47

answered Apr 08 '17 at 10:34

Gowtham Shiva

3,802
2
11
27

score 1 · Answer 3 · answered Apr 08 '17 at 10:35

1

Use Find and Replace Function in Excel, Use this

Find What: 'NULL'

Replace With: NULL

answered Apr 08 '17 at 10:35

Satish Babariya

3,062
1
15
29

Thanks, this is indeed a solution but i wanted to do it programmatically ! – Dennis Apr 08 '17 at 10:39
@Dennis You can still do it that way and record a macro. Then Excel will write the VBA code for you. – Ralph Apr 08 '17 at 12:13

Remove single quotes around string in excel

3 Answers3