Removing unicode character from cells 2

[davedave24]

I have a multiline textbox for entering an address, which gets pasted into multiple cells. On my home machine, this works fine, but on the work machine, this strange unicode looking character appears at the end of each line:

Here's the code I use to break down the address into multiple cells:

    Dim Str As String, a
    Dim cnt As Integer
    Dim w()
    Dim k As Integer
            
    'Customer address
    Str = textCustomerAddress.Value
    a = Chr(10)
    cnt = UBound(Split(Str, a))
             
    ReDim w(1 To cnt + 1, 1 To 1)
             
    For k = 0 To cnt
        w(k + 1, 1) = Split(Str, Chr(10))(k)
    Next k

    .Range("B8").Resize(k, 1) = w

How do I make this unicode not paste into the cell?

 

[combo]

Multiline textbox uses vbCrLf to break lines (ASCII codes 13 and 10). In your code you left characters with code 13.
You can try with:
a = vbCrLf

combo

 

[davedave24]

Doesn't seem to work combo

 

[combo]

Test the text you have in the multiline text box:
For i=1 To Len(Str)
Debug.Print Asc(Mid(Str,i))
Next i

combo

 

[Andrzejek]

If it works OK on your home machine, but not at work, it maybe something different with the Excel set up. It maybe just the error checking option turned on:

1.Click the Office button (File in 2010).
2.Click the Excel Options button.
3.Click Formulas in the left column.
4.Compare Error Checking rules on the home computer vs work machine

Just a guess here....


Have fun.

---- Andy

 

[combo]

Also, try to change the argument in the second Split:
w(k + 1, 1) = Split(Str, vbCrLf)(k)

combo

 

[davedave24]

Cheers guys, the last post by combo worked great