Your data sample resembles JSON.

That's why we can convert it into a legit JSON, and easily tokenize it after that into separate columns.

It will work starting from SQL Server 2016 onwards.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, tokens NVARCHAR(MAX));
INSERT @tbl (tokens) VALUES
('[04/11/2023,"New addition","","new additional of fund","This, Adam, Kyle","5.00","11147474"]');
-- DDL and sample data population, end

SELECT ID --, j
    , JSON_VALUE(j, '$[0]') AS col0
    , JSON_VALUE(j, '$[1]') AS col1
    , JSON_VALUE(j, '$[2]') AS col2
    , JSON_VALUE(j, '$[3]') AS col3
    , JSON_VALUE(j, '$[4]') AS col4
    , JSON_VALUE(j, '$[5]') AS col5
    , JSON_VALUE(j, '$[6]') AS col6
FROM @tbl AS t
CROSS APPLY (SELECT STUFF(STUFF(tokens,12,0, '"'),2,0,'"')) AS t1(j);

Output

You can use this script:

DECLARE @Str VARCHAR(200) = '[04/11/2023,"New addition","","new additional 
of fund","This, Adam, Kyle","5.00","11147474"]'
SET @Str = SUBSTRING(@Str, 2, LEN(@Str)-1)
SET @Str = SUBSTRING(@Str, 1, LEN(@Str)-2)
SET @Str = REPLACE(@Str, '","', ';')
SET @Str = REPLACE(@Str, ',"', ';')
SELECT * FROM STRING_SPLIT(@Str,';')