How to replace this character ...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Hi Harish

Check out Data>Text 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)




Harish Mohanbabu said:
Hi,

Recently I received a huge list of customers (around 3000) from my accounts department. This excel document contains 2
Click to expand...
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
Click to expand...
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.
 
-----Original Message-----
Hi,

Recently I received a huge list of customers (around
Click to expand...
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
Click to expand...
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
.
Click to expand...

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.
 
Back
Top