help please ....tidy up some data

[mrowe]

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

 

[dk]

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]

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]

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]

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]

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]

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]

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

 

[PaddyD]

143,13 - such a small difference