looking for formula for converting a 2 step process

[alalexbr]

ok so im looking for help on create a formula on the following setup


i currently have keywords that are hypen seperated and then there is a space and then the next keyword begins -


example below and lets say i place all of these keywords on cell a2


big-car rainy-day giant-woods big-car broken-middle-finger yellow-mustard-coat furry-little-animal


what im looking to do is take cell a2 with all the keywords and have them reformated in the example below in cell a3


big car, rainy day, giant woods, big car, broken middle finger, yellow mustard coat, furry little animal




so im looking for a formula that will remove the hypen and insert a comma as shown above
any info will be greatly appreciated


thank you

 

[lrobbo314]

This custom function should do the trick.

Function Reformat(iStr As String) As StringDim SP() As String
Dim Result As String


SP = Split(iStr, " ")
Result = Replace(Join(SP, ", "), "-", " ")
Reformat = Result
End Function

Hit Alt+F11 to open the VBA editor.
Hit Alt+I+M to insert a new module.
Then paste the code above.

Then, back in your worksheeet, the formula will look like this...

=Reformat(A2)

 

[lrobbo314]

Or this regular formula.

=SUBSTITUTE(SUBSTITUTE(A2," ",", "),"-"," ")

 

[alalexbr]

thank you . i followed your instructions and when i went to paste the formula in cell a3 and hit return visual basic window popped up and said compile error - expected: end of statement. please advise

This custom function should do the trick.

Function Reformat(iStr As String) As StringDim SP() As String
Dim Result As String


SP = Split(iStr, " ")
Result = Replace(Join(SP, ", "), "-", " ")
Reformat = Result
End Function

Hit Alt+F11 to open the VBA editor.
Hit Alt+I+M to insert a new module.
Then paste the code above.

Then, back in your worksheeet, the formula will look like this...

=Reformat(A2)

 

[alalexbr]

ahh this one worked like a charm thank you so much!!!

Or this regular formula.

=SUBSTITUTE(SUBSTITUTE(A2," ",", "),"-"," ")

 

[lrobbo314]

Not sure why you were getting on error with the VBA. Oh well, glad the formula version worked for you.

 

[StephenCrump]

Not sure why you were getting on error with the VBA.

It should work fine if coded:

Function Reformat(iStr As String) As String
Dim SP() As String
...
'rather than verbatim as posted
Function Reformat(iStr As String) As StringDim SP() As String
...