removing line breaks in Excel columns 1

[fcoomermd]

This is what I need to do.
I have a column with a text formatting like this:
Billy Bob
Address: 2343 Anywhere Ave
Phone: 654-6548
Ciy: Springfield
State: Utah
Country: USA
ZIP: 90210

=-=-=-=-
Now, I want it all on the same line
Address: 2343 Anywhere Ave Phone: 654-6548 Ciy: Springfield State: Utah Country: USA ZIP: 90210;
How can I do this to the entire document in one steP?
Thanks so much
Fred

 

[SkipVought]

Hi,
What delimits one set of data from the next? Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[fcoomermd]

I just used that as an example, it is mostly names going in the fields for companies. So I dont need a delimter... However, it would be nice to insert one... But it is no big deal. I just need it all converted to one line. I am importing this into a SQL Server Database
Thanks
Fred

 

[SkipVought]

So there will ALWAYS be 7 rows of data per record? Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[fcoomermd]

This is all in one field.
Example
FieldName: Contact
Contents: Billy Bob
Address 2343 Anywhere Ave
Phone 654-6548
Ciy Springfield
State Utah
Country USA
ZIP 90210

And I want to remove the line breaks, cause it seems that when I import to sql server, it is treating the breaks as many multiple spaces... Then it becomes a data type size issue. There can be any amount of data for each column.
Thanks

 

[SkipVought]

How 'bout this...

Sub RemoveLF()
    ActiveSheet.Columns("A").Replace _
        What:=vbLf, Replacement:="", _
        SearchOrder:=xlByColumns, MatchCase:=True
End Sub

Hope this helps Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[fcoomermd]

I am sorry, I rarely work with Excel... Where do I call this sub from?

 

[SkipVought]

alt+F11 opens the Visual BASIC Editor.

Paste the procedure in the code window for the worksheet object that you have these cells on.

close the vbe window

Tools/Macros/Macro - and run the procedure

This procedure will perform the change on all cells in COlumn A. If you have other columns, then modify the .Columns("A&quot. expression accordingly ...

Columns("A:C").

would include columns A to C Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[fcoomermd]

Thanks... that did exactly what I was looking for... However, the problem has not been solved... My main goal is to import the data into a sql server... But I am getting an error that the data field is too small to except the data.
I am not sure what the problem is. I have tried several datatypes, including varchar(255), then had gone all the way upto (8000)... And unfortunately, no results... Any suggestions?
Thanks... you have been helpful thusfar
Fred

 

[SkipVought]

Each data element has to be handled separately -- some data is numeric (and numeric data comes in various types), text, date/time, logical.

How are you trying to get this data into the database? I would think that you would FIRST need to get each data element in a single column -- each column with similar data -- Name, Address, Phone, City, State, Country, ZIP -- 7 columns of data. You can get there using the Date/Text to Columns Wizard. Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com