If you have to import an excel file into a database table you have different ways. Recently I faced with an excel file with inside cells with single quote inside the text and some amount cells with zero value.
Here you can find my formula, you can use it a basic formula for your case.
Formulas are in italian but you can replace them:
SOSTITUISCI with REPLACE
TEST with TEXT
CODICE.CARATT with I really don't know but it return a char starting from ascii value. In our case return the single quote for ascii 39 and blank space for ascii 32.
="INSERT INTO [dbo].[FECasFis] ([TIPDOC],[NUMDOC],[DATDOC],[PARIVA],[RAGSOC],[IMPONIB],[IMPOSTA],[SDIFILE],[STATOCONS],[DATRIC],[BOLLO]) VALUES ("
& "'" & SOSTITUISCI(B2,CODICE.CARATT(39),CODICE.CARATT(32)) & "'" & ","
& "" & C2 & "" & ","
& "'" & ANNO(D2) & "-" & MESE(D2) & "-" & GIORNO(D2) &"'" & ","
& "" & E2 & "" & ","
& "" & "'" & SOSTITUISCI(F2,CODICE.CARATT(39),CODICE.CARATT(32)) & "'" & ","
& TESTO(G2,"#########0.##0") & ","
& TESTO(H2,"#########0.##0") & ","
& "'" & I2 &"'" & ","
& "'" & J2 &"'" & ","
& "'" & ANNO(K2) & "-" & MESE(K2) & "-" & GIORNO(K2) &"'" & ","
& "'" & L2 &"'"
& ")"
and here the SQL Insert generated from Excel formula
INSERT INTO [dbo].[FECasFis]
([TIPDOC],[NUMDOC],[DATDOC],[PARIVA],[RAGSOC],[IMPONIB],[IMPOSTA],[SDIFILE],[STATOCONS],[DATRIC],[BOLLO])
VALUES
('','40 001','2021-1-31','01468220122',' FONTANELLA SAS DI A. REZZONICO & C. ',44.80,9.860,'4509841433','Fattura consegnata','2021-2-8','')
