How to replace this character ...
 

Hi

Recently I received a huge list of customers (around 3000) from my accounts department. This excel document contains 2 columns, viz Customer & Address. I am supposed to export this data to another system

The problem is this address column contains actually 4 fields (Door No, Name, City & County). All these fields are concatenated with a square symbol (unable to copy & paste that symbol) and pasted into the Address column. I think this symbol is carriage return character. Now I want to split this particular column into 4 separate columns mentioned above
How can I do this? Can someone let me know please..

Thanks in advance

Harish Mohanbabu

How to replace this character ...
 

Hi Harish

Check out DataText to columns in the Menubar

Try this to replace the carriage return character

Sub testme()
With ActiveSheet
.Cells.Replace What:=Chr(13), Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End With
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Harish Mohanbabu" wrote in message
...
Hi,

Recently I received a huge list of customers (around 3000) from my accounts department. This excel document contains 2
columns, viz Customer & Address. I am supposed to export this data to another system.

The problem is this address column contains actually 4 fields (Door No, Name, City & County). All these fields are
concatenated with a square symbol (unable to copy & paste that symbol) and pasted into the Address column. I think this symbol
is carriage return character. Now I want to split this particular column into 4 separate columns mentioned above.
How can I do this? Can someone let me know please...

Thanks in advance,

Harish Mohanbabu

How to replace this character ...
 

-----Original Message-----
Hi,

Recently I received a huge list of customers (around
3000) from my accounts department. This excel document
contains 2 columns, viz Customer & Address. I am supposed
to export this data to another system.

The problem is this address column contains actually 4
fields (Door No, Name, City & County). All these fields
are concatenated with a square symbol (unable to copy &
paste that symbol) and pasted into the Address column. I
think this symbol is carriage return character. Now I
want to split this particular column into 4 separate
columns mentioned above.
How can I do this? Can someone let me know please...

Thanks in advance,

Harish Mohanbabu
.


You can use the InStr Function and search enter the search
string as Chr(0)

Position = InStr(Cells(x, y), Chr(0))

Then use the Left Function to copy the Leftmost characters
into another Celll and the Right Function to extract the
remaining characters in the string into a string variable
and loop through the rest of the remaining string until
all of the squares are removed from that string. Then move
on to the next Cell with squares and do the same.