Discuss "excel to access need macro to find space" in the Microsoft Access forum.

peebman2000 · #1 August 27th, 2008, 09:16 AM

Good morning everyone its peeb, i'm really not familiar with doing macros, especially in excel. I have friend that is working on an excel sheet that will be imported into access. She has like over 100 + zipcodes that she has entered into 1 cell in excel. Well she need to put a comma between the spaces of each zipcode. This is her first time working with a macro, I told I would see what I can research on it. I"m not finding much online except for

http://www.mrexcel.com/forum/showthread.php?p=1543776
Unfortantly I don't fully understand it, but I really have not sat an analized the code. I'm sure If I did with time i would.

Anyway does anyone know of like a simple macro snippet that can search a cell for spaces and insert a comma in between the spaces of zip codes.

(e.g., 44444, 44444, 44444)

I appreciate any suggestions, thanks.

richyrich · #2 August 27th, 2008, 09:42 AM

Try a function like this:-

Code:

Function add_comma(cell_value As String) As String
 
add_comma = Replace(cell_value," ",", ")
 
End Function

This needs to be added as a module in VBA.

Then in a seperate cell on the spreadsheet have

Code:

=add_comma(A1)

Obviously where A1 is the cell that contains the data you want to seperate.

Hope that helps.

peebman2000 · #3 August 27th, 2008, 10:34 AM

Hey Richy, thanks for the help. I just tried it in excel and the code works perfectly. I just forwarded my friend this link with the code for her to use and told her to call me if she needs help on it.

But thanks again dude I appreciate the reply. Have a good day.

Quote:

Originally Posted by richyrich

Try a function like this:-

Code:

Function add_comma(cell_value As String) As String
 
add_comma = Replace(cell_value," ",", ")
 
End Function

This needs to be added as a module in VBA.

Then in a seperate cell on the spreadsheet have

Code:

=add_comma(A1)

Obviously where A1 is the cell that contains the data you want to seperate.

Hope that helps.

richyrich · #4 August 27th, 2008, 10:44 AM

Quote:

Originally Posted by peebman2000

Hey Richy, thanks for the help. I just tried it in excel and the code works perfectly. I just forwarded my friend this link with the code for her to use and told her to call me if she needs help on it.

But thanks again dude I appreciate the reply. Have a good day.

No problem...

peebman2000 · #5 August 28th, 2008, 09:59 AM

Hey Rich got another question. I worked with my friend yesterday on the excel macro and it worked thanks to your idea. But she also has zipcodes that have breaks (vbCrLf) see exampleL
55555
45666
57555

I tried playing with your VBA code below to see if I could get it to replace line breaks with commas.

Code:

Function add_comma(cell_value As String) As String
 
add_comma = Replace(cell_value,"vbCrlf ",", ")
 
End Function

And that didn't work. Is there a way to have the macro go through and look for line breaks and replace them with commas?

Quote:

Originally Posted by richyrich

No problem...

richyrich · #6 August 28th, 2008, 10:48 AM

I presume she has used Alt+Enter to put in each line. In which case, try this:-

Code:

Function add_comma(cell_value As String) As String
 
        add_comma = Replace(cell_value, Chr(10), ", ")
 
End Function

And then call it in the same way.

Hope that helps.

peebman2000 · #7 August 28th, 2008, 11:29 AM

Thanks again rich that worked, I just tried in excel and it worked. My friend is working on it now. Thanks again for the help.

Quote:

Originally Posted by richyrich

I presume she has used Alt+Enter to put in each line. In which case, try this:-

Code:

Function add_comma(cell_value As String) As String
 
        add_comma = Replace(cell_value, Chr(10), ", ")
 
End Function

And then call it in the same way.

Hope that helps.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
From excel to database/web app	Rebelle	Database Design Help	7	July 10th, 2008 01:36 PM
find words and change	todd2006	JavaScript Programming	4	July 2nd, 2008 09:58 AM
Excel question	Rebelle	Microsoft Office	11	May 27th, 2008 07:22 PM
Retrieving Web Data using Excel	mehere	Microsoft Office	13	May 22nd, 2008 03:14 PM

The Following User Says Thank You to richyrich For This Useful Post:
peebman2000 (August 27th, 2008)

The Following User Says Thank You to richyrich For This Useful Post:
peebman2000 (August 28th, 2008)

Discuss "excel to access need macro to find space" in the Microsoft Access forum.

Microsoft Access - Microsoft Access is a database for small to medium applications. Learn tips and tricks and best database practices here.