Split Data

Tap

New Member
Joined
Feb 28, 2002
Messages
14
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
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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
 
Upvote 0
I tried that but find dont work
when the filed is expanded the | turns in to a little square box

any suggestions?
 
Upvote 0
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 "|".
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top