Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Delimiter problem

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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)

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    New Member
    Join Date
    Feb 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Juan Pablo

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

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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)
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  5. #5
    New Member
    Join Date
    Feb 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    How do I look at the ASCII code?

  6. #6
    MrExcel MVP DRJ's Avatar
    Join Date
    Feb 2002
    Location
    California
    Posts
    3,853
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  8. #8
    New Member
    Join Date
    Feb 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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).
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  10. #10
    MrExcel MVP DRJ's Avatar
    Join Date
    Feb 2002
    Location
    California
    Posts
    3,853
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •