Use Replace function

[bjshots]

I am confused with the replace function. I have data in a cell "May,12,2015" I want to replace the first comma with a space but I keep getting errors with the code. Below is the code that I have

MyString = Range(MyDateCol & MyDateRow).Value
MyString = Replace(MyString, 4, 1, " ")
Range(MyDateCol & MyDateRow) = MyString


I did not want to replace the character " , " in the code because I only wanted to replace the first one.
I also need a space after the second comma

The above data is imported from another program and will always show in this format.

thanks for any help

 

[pgc01]

Hi

This statement has wrong syntax.

MyString = Replace(MyString, 4, 1, " ")

The fourth parameter of Replace is a number value, you have a string value (a space).

I think you meant:

MyString = Replace(MyString, ",", " ", Count:=1)

Please check the help for the Replace() function.

 

[pgc01]

P. S.

I posted why your code is not working and a correction to the code.

As a solution for your problem, however, I would suggest something different.

You want to change the 4th character of the string value in the cell to " ".

Instead of your 3 statements, I would use:

Range(MyDateCol & MyDateRow).Characters(4, 1).Text = " "

 

[DocAElstein]

.... I have data in a cell "May,12,2015" I want to replace the first comma with a space ..... I only wanted to replace the first one.....
I also need a space after the second comma.....

Before:

Using Excel 2007

Row\Col	A​
1​	May,12,2015

Tabelle2

After:

Row\Col	A​	B​	C​
1​	May,12,2015	May 12,2015	May 12, 2015
2​

Tabelle2

Code:

Sub Replace1CommerAndPutSpaceAfterOther() 'http://www.mrexcel.com/forum/excel-questions/906909-use-replace-function.html?
Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Tabelle2")
Dim MyString As String
MyString = ws.Range("A1").Value
MyString = Replace(MyString, ",", " ", , 1) 'Replace in Mystring,   a commer   with  ,  a space   ,   ,   only once
ws.Range("B1").Value = MyString
MyString = Replace(MyString, ",", ", ")
ws.Range("C1").Value = MyString
'Or
ws.Range("C1").Clear
ws.Range("C1").Value = Replace(Replace(ws.Range("A1").Value, ",", " ", , 1), ",", ", ")
End Sub

Remember to change
Tabelle2
to your sheet name ( as seen in the Tab at the bottom of your excel Spreadsheet window)

Alan

 

[bjshots]

Thanks pgc01 and DocAElstein for your help. The best solution for me is the character function. Thanks for helpiing me understand the replace function better

 

[DocAElstein]

.... Thanks for helpiing me understand the replace function better

Gald we could help
And thanks for the feedback