Excel Macro

[computerman29642]

I have values entered in the C column that look like the following

'MAK, REP'

I need to find a way to make the values like the following:

'MAK','REP'

Any suggestions would be very much appreciated.

 

[draceplace]

Somthing like this...=SUBSTITUTE(B3,",","','",1)
Substituting tic comma tic for comma

 

[computerman29642]

What if there are more than two values in a string? Would I have to do some type of loop? If so, how would I go about doing that?

 

[draceplace]

how about an example of what your talking about. Also over on your SQL question would you post the code that didn't work for your? Looks like you would take it either way? Is the data on SQL, is there some reason these comma delelmited fields aren't separate fields?

 

[computerman29642]

draceplace,

thank you for the replies. I am trying to resolve the issue for both excel and sql. The field is on a form that I have no control over. There could be many values inserted into the field separated by commas. That's why I am trying to find a way to separate them as individual strings.

 

[draceplace]

Not sure what you mean By 'individual strings' . would removing the tic ' marks be a vialble solution?

 

[computerman29642]

The value in the field is like 'RIP,MIK,TRU'. I need them to be stored in a variable (or something like that) as 'RIP','MIK','TRU'

The number varries. Next time it could be four.

 

[draceplace]

If you remove the 1(number of occurance) from the end of the Substitute function it will act on all commas =SUBSTITUTE(A2,",","','")

would RIP,MIK,TRU not be as usefull? I ask because thats the way I would want it. That SUB is
=SUBSTITUTE(A3,"'","")

In SQL you need something like this
Update dbo.mytable
Set v_test = replace(v_test, ',', '','')

I didn't test this it may need to be
replace(v_test, 'char(2c)', 'Char(27)+Char(2c) +Char(27)')

 

[computerman29642]

draceplace,

Unforunately, I need the ticks.

I will test the codes you gave me.


I really appreciate the help

 

[computerman29642]

draceplace,

I was unable to get the SQL code to work properly.

 

[computerman29642]

Is there a way to do some type of loop?

 

[draceplace]

I pretty busy today, but if I get a chance to play in SQL ...What code did you try and what was the issue?

 

[computerman29642]

I tried the code you provided. I tried alter it a little as well.

The results was that I still only got back the first two values. If there were three or four it did not get those.