Excel Macro

Status
This thread has been Locked and is not open to further replies. The original thread starter may use the Report button to request it be reopened but anyone else with a similar issue should start a New Thread. Watch our Welcome Guide to learn how to use this site.

computerman29642

Thread Starter
Joined
Dec 4, 2007
Messages
2,895
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.
 
Joined
Jun 8, 2001
Messages
2,583
Somthing like this...=SUBSTITUTE(B3,",","','",1)
Substituting tic comma tic for comma
 

computerman29642

Thread Starter
Joined
Dec 4, 2007
Messages
2,895
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?
 
Joined
Jun 8, 2001
Messages
2,583
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

Thread Starter
Joined
Dec 4, 2007
Messages
2,895
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.
 
Joined
Jun 8, 2001
Messages
2,583
Not sure what you mean By 'individual strings' . would removing the tic ' marks be a vialble solution?
 

computerman29642

Thread Starter
Joined
Dec 4, 2007
Messages
2,895
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.
 
Joined
Jun 8, 2001
Messages
2,583
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

Thread Starter
Joined
Dec 4, 2007
Messages
2,895
draceplace,

Unforunately, I need the ticks.

I will test the codes you gave me.


I really appreciate the help
 
Joined
Jun 8, 2001
Messages
2,583
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

Thread Starter
Joined
Dec 4, 2007
Messages
2,895
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.
 
Status
This thread has been Locked and is not open to further replies. The original thread starter may use the Report button to request it be reopened but anyone else with a similar issue should start a New Thread. Watch our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top