Split Data

[Tap]

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

 

[mrowe]

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

 

[Tap]

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

any suggestions?

 

[Aladin Akyurek]

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 "|".

 

[Tap]

Not sure what to do with this?

 

[Aladin Akyurek]

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.

 

[Tap]

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

 

[Aladin Akyurek]

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

 

[Tap]

A BIG BIG thank you
it did the job wonderuelly


Thanks again

Tap