Delimiter problem

[osullj]

Hi,

I work in a group of 8 people. We are moving from Outlook to Goldmine. I exported the 8 contact databases from Outlook to Excel and have been cleaning and enhancing the data in excel, eliminating duplicates, using consistent company names and so on. When fully cleaned I will import into Goldmine as a starting common database for all 8.

I have hit a problem trying to use Excel's Text to Columns feature. Many of the address records have imported into excel in one column, with  as a delimiter between address line 1 address line 2 etc. When I try to get Excel's Text to Columns to recognise this , I cannot. I can use Character MAp to get a  in the dialog box but Excel ignores it and I'm left with just the first line after the process runs with subsequent address lines being lost.

How can I proceed to split up my address lines into separate fields

Thanks.....John (Dublin, Ireland)

 

[Juan Pablo González]

Can you search and replace that character ? if you can, replace it with something that doesn't appear anywhere else, like "*" or "$" or something similar.

 

[osullj]

Thanks Juan Pablo

I tried this but I couldn't get the  into the Replace dialog box.

 

[Juan Pablo González]

Can you look at the ASCII code of that character ? VBA can replace it easilty.

Cells.Replace What:=Chr(14), Replacement:="$"


Replace the 14 with the actual number. (It may be a 10 or a 13)

 

[osullj]

How do I look at the ASCII code?

 

[DRJ]

What you can do is copy the character that you want to replace and then in the replace dialog Paste (Ctrl + C) it and it will show up then you can replace with what you want. I tried it and when I copied the square character and pasted it in the replace dialog it showed up as a circle with a squiggly line on top, but it worked fine while replacing.

HTH

DRJ

 

[Juan Pablo González]

On 2002-02-27 08:39, osullj wrote:
How do I look at the ASCII code?

In the character map, look at the number that appears at the bottom left. "Alt xxxx"

Or paste the strange symbol to Excel, and use this formula

=CODE(A1)

where A1 has the symbol.

 

[osullj]

Thanks DRJ. I know it sound crazy but when I do that the paste in to replace dialog box come up blamk.

I used =CODE to determine that the ASCII number is 10. Juan Pablo you gave me the vba script above. I have never used VBA so how do I use it to do this.

Thanks John

 

[Juan Pablo González]

Hit Alt F11
Hit Control G

There you should see a blank window titled "Inmediate".

Paste this there:

Cells.Replace What:=Chr(10), Replacement:="$"

and hit Enter (While the cursor is in that line).

 

[DRJ]

Hi

To use VBA hit ALT+F11 to open the VB editor then go to insert then module.

In the module type this

Sub MyCode()


End Sub


Put your code in between those two lines and then press the play button to run the macro.

HTH

DRJ