Instead of C:\SQLDBA\ServerName\Instance rather C:\SQLDBA\ServerName_Instance I am trying to create a folder based on the servername\instance to backup up to, i want the folder to create as servername_instance. Could you please point out where i am going wrong as it creates as servername\instance   

IF OBJECT_ID('TempPSCreateFolder') IS NULL
BEGIN
CREATE TABLE [TempPSCreateFolder]
(QueryText nvarchar(max))
END

INSERT INTO [TempPSCreateFolder]

SELECT '$fso = new-object -ComObject scripting.filesystemobject

$fso.CreateFolder("C:\'+ STUFF((SELECT ',' + SUBSTRING(@@SERVERNAME, 1, CASE 
WHEN CHARINDEX(',', @@SERVERNAME) &gt; 0 THEN 
CHARINDEX(',', @@ServerName)-1
                       ELSE LEN(@@ServerName)
                    END)

FOR XML PATH('')), 1, 1, '')+'")'

EXEC sp_configure 'xp_cmdshell', 1;  
RECONFIGURE

EXEC xp_cmdshell 'bcp master.dbo.TempPSCreateFolder out 
"C:\SQLDBA\CreateFolder.ps1" -c -T -SServer\Instance'

EXEC sp_configure 'xp_cmdshell', 0;  
RECONFIGURE

DROP TABLE TempPSCreateFolder
Your script seems unduly complicated. All you need to do to format the folder name according to the title of your question is replace the backslash in @@SERVERNAME with an underscore. No need for the table, script file, and XML PATH for this task. PowerShell also includes a New-Item command to avoid the need for the legacy COM FileSystemObject.

DECLARE @PowershellCommand varchar(8000) = 'powershell -Command "New-Item -Path \"C:\SQLDBA\'
    + REPLACE(@@SERVERNAME, '\', '_')
    + '\" -ItemType \"directory\""';
EXEC sp_configure 'xp_cmdshell', 1;  --assumes 'show advanced' config option is already enabled 
RECONFIGURE;
EXEC xp_cmdshell @PowershellCommand;
EXEC sp_configure 'xp_cmdshell', 0;  
RECONFIGURE;


You could also avoid xp_cmdshell entirely and just run a stand-alone PowerShell script on the server:

New-Item -Path "C:\SQLDBA\ServerName_InstanceName" -ItemType "directory"