How to save an excel in a pipe delimited format?

**princesouvik** · 07-07-2009, 01:22 AM

Hi Friends,
I have an excel sheet which I need to store as a pipe delimited text file.
This should be done using macro.
For example:
input:

45 56 78 abcd

output:

45|56|78|abcd|

I cannot create a tab delimited or csv file and then replace manually. It has to be done automatically.

Please Help!!

**Leith Ross** · 07-07-2009, 01:43 AM

Hello princesouvik,

I have a few questions about what you want to do.
1. In your example, is each cell in a row to separated by a pipe value?
2. Are the number of fields in each row the same?
3. Does text need to be enclosed in quotes or preceeded with an accent mark (`)?
4. Do numbers need to include separators like decimal points and commas?

**princesouvik** · 07-07-2009, 02:04 AM

The following is a sample input:

004011641391 1040 d 324.63 09/06/2009 DEC
004005562228 1040 d 134.66 01/05/2009 OTH
004015902314 1040 d 801.83 06/06/2009 GNA
004029976905 1040 d 1625.83 09/06/2009 DEC
004026411232 1040 d 494.82 06/06/2009 OTH

The following is a sample output:

004011641391|1040|d|324.63|09/06/2009|DEC||
004005562228|1040|d|134.66|01/05/2009|OTH||
004015902314|1040|d|801.83|06/06/2009|GNA||
004029976905|1040|d|1625.83|09/06/2009|DEC||
004026411232|1040|d|494.82|06/06/2009|OTH||

Moreover the input file has the same number of fields in each row.
No quotes are present, or any special char, except "/" in date field
Decimal points may and may not be there.
Hope this was helpful.

Thanks

**princesouvik** · 07-08-2009, 12:40 AM

Guys any help with this???!

**bgunawan** · 07-08-2009, 03:11 AM

Hi princesouvik,

Copy and paste the following code and it should work.

PHP Code:


Columns("A:A").Select 
 
    Selection.Replace What:=" ", Replacement:="|", LookAt:=xlPart, _ 
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ 
        ReplaceFormat:=False

**Jbentley** · 07-08-2009, 03:13 AM

Are you able to post a small sample spreadsheet?

It's still unclear whether you input data is a single cell (with output elements currently separated with a space) or inf each element is in a separate cell.