Split Data
Amazing chart utilities from Jon Peltier
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Split Data

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

    Default

    HI I have one column in a spreadsheet which holds address details, I need to split this in to different columnd
    I tried Text to column but that seems to truncate some of the data

    there is a delimiter which is | and ||
    which represent new lines

    I have tried everything but canmt get it to work
    here is what the data looks like

    Contact-1234||Mr John Doe|Doe and Company|Doe House|1 The Mall|West Street|Zip 123||Tel: 0123456789||Description

    Thaks in advance


  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    232
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You could do a find and replace on "|", changing it to ";".

    You might find you get some changes like ";;" so just do a find and replance on "::" to ";"

    Save you spread sheet as .txt. Open the txt file with excel and follow the wizard using the Delimited option and select the semicolon as the separator.

    This will put split out your data.

    Hope that helps

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

    Default

    I tried that but find dont work
    when the filed is expanded the | turns in to a little square box

    any suggestions?

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,775
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-03-01 09:54, Tap wrote:
    I tried that but find dont work
    when the filed is expanded the | turns in to a little square box

    any suggestions?
    What do you get if you apply to the example text which is now in Excel:

    =CODE(MID(A1,13,1)) ?

    Modify and apply the above to char "|".

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

    Default

    Not sure what to do with this?

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,775
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-03-02 02:48, Tap wrote:
    Not sure what to do with this?
    You said "when the filed is expanded the | turns in to a little square box". That means "|" is probably not a bar but something else. The CODE formula will tell us what char that "|" is.

    That formula works like this:

    In A1 enter: Tap

    When I want to know the ascii code of the third char/letter van the string in A1, I apply:

    =CODE(MID(A1,1,3))

    which returns 116.

    Look at hhtp//www.asciitable.com

    to see what letter is encoded with 116. And You'll understand what I'm after.

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

    Default

    Brilliant thankyou I now nknow the the ascii value which is 13
    but how can I change it to 172 which is

    I looked in help it says to go to convtext.exe I dpnt seem to have that file?
    is ther another way

    thank you

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,775
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-03-02 14:40, Tap wrote:
    Brilliant thankyou I now nknow the the ascii value which is 13
    but how can I change it to 172 which is

    I looked in help it says to go to convtext.exe I dpnt seem to have that file?
    is ther another way

    thank you
    Tap,

    Step 1

    In B1 enter:

    =SUBSTITUTE(A1,CHAR(13),"@")

    Copy down this to replace the undesired char by @.

    Step 2

    Select all of the relevant cells in B, copy the selection, and execute Edit|Patse Special >Values over itself (thus in place).

    Step 3

    Apply to B-cells using the option Data|Text to Columns >Delimited and typing in the Other box "@" (without quotes) as delimiter.

    Aladin





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

    Default

    A BIG BIG thank you
    it did the job wonderuelly


    Thanks again

    Tap

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
  •  

 

DMCA.com