help please ....tidy up some data

mrowe

Board Regular
Joined
Feb 17, 2002
Messages
232
Hi, I've got a large list of data that imports like the example below. I've been asked to split the record into more than one collumn (i.e Addr1, Addr2,County,Country,PostCode)

I've tried substitue and replace to get rid of the � but it's not working. Anyone got an idea of how to tidy this up?


"Acresfield�St Ann's Square� 8-10 Exchange Street� Manchester�"

Thanks in advance
Matt
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
Have you tried selecting the cell(s) and choosing Data, Text to Columns? The � character could possibly be a tab character and you can choose this as your delimiter.
 

mrowe

Board Regular
Joined
Feb 17, 2002
Messages
232
Hi Dan,

No luck. The � represents a carriage return, I think so it just ran the list out of sync with the other records to its side.

Find and Replace doesn't work either... anyone else got an idea?

Thanks
Matt
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
can you not do a find replace - and find all the boxes and replace with a comma. Then do the data - columns with , as delimiter.
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234

ADVERTISEMENT

this might do it:

1) replace the errant character with something more pleasant:

=SUBSTITUTE(A1,CHAR(143),"/")

2) do the text to cols on the result...

paddy
 

mrowe

Board Regular
Joined
Feb 17, 2002
Messages
232
Hi Paddy,

That's the line I was thinking of. Thanks, but one small problem CHAR143 doesn't equal return, nor does CHAR144 - I tried finding a list of what character combinations = function i.e CHAR143 = return, but no luck - is there such a list - if so, this will solve it (I hope!!!)

Thanks everyone for your help
Matt
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234

ADVERTISEMENT

I assusme that you have text entires in a field(s) that contain the errant character embedded. On the example you posted, there is one such character at position 11 in the string.

=code(mid,a1,11,1))

returns 143. Therefore,

=SUBSTITUTE(A1,CHAR(143),"/")

applied to

Acresfield�St Ann's Square� 8-10 Exchange Street� Manchester�

returns

Acresfield/St Ann's Square/ 8-10 Exchange Street/ Manchester/

what an I missing?

paddy
 

mrowe

Board Regular
Joined
Feb 17, 2002
Messages
232
Hi Paddy,
Your formula was right, apart from the CHAR143.

Couldn't find a list so I recorded a macro that copied the cell and I got these results

ctiveCell.FormulaR1C1 = _
"Hollingworth Road" & Chr(13) & "Littleborough" & Chr(13) & "Lancashire" & Chr(13) & " " & Chr(13) & " " & Chr(13) & "OL15 0BA"
Range("D3").Select
ActiveSheet.Paste

Chr(13) is the one I needed.... guess that's a handy tip if you can't find what the CHAR name is anyway...

Really appreciate your help, this has saved me HOURS of work.

ta
Matt
 

Forum statistics

Threads
1,144,510
Messages
5,724,784
Members
422,579
Latest member
Lekha mohanty

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
Top