CODEWITHSUNDEEP
sqlexcelfunction
Przemyslaw Remin
Przemyslaw Remin

Reputation: 6940

stuff function in excel

Is there an equivalent of SQL function STUFF in Excel or Excel VBA? I want exact function shown here with all the parameters, especially parameter length: https://learn.microsoft.com/en-us/sql/t-sql/functions/stuff-transact-sql

Upvotes: 1

Views: 3522

Answers (2)

Mrig
Mrig

Reputation: 11727

Syntax of STUFF is STUFF( source_string, start, length, add_string )

Solution 1

Using formula

=LEFT(A1,C1-1) & B1 & MID(A1,C1+D1,LEN(A1)-C1-D1+1)

where,
Cell A1 - source_string
Cell B1 - add_string
Cell C1 - start
Cell D1 - length

Solution 2

Using UDF in excel VBA 

Public Function Stuff(textStr As String, start As Long, count As Long, replaceStr As String) As String
    Stuff = Left(textStr, start - 1) & replaceStr & Mid(textStr, start + count)
End Function

where,
textStr - source_string
start - start
count - length
replaceStr - add_string

Here, start > 0 and count >= 0.

enter image description here

Upvotes: 4

lovelace
lovelace

Reputation: 1205

Try 'replace' function in Excel

Excel: =REPLACE("To be or not to be", 1, 18," that’s the question")

Upvotes: 0

Related Questions