text to columns isn't detecting space

[mberow]

I have Excel2007 running on Windows XP.

I'm trying to use text to columns to parse a column of data. When I check space as the delimiter, it doesn't parse. The third step just shows one column of data. Below is a cut/paste of one cell.

<table width="257" border="0" cellpadding="0" cellspacing="0"><col width="257"><tr height="19"> <td style="height: 14.65pt; width: 193pt;" width="257" height="19">ROCK TOP DRINK</td><td style="vertical-align: top;">
</td> </tr><tr><td style="vertical-align: top;"></td><td style="vertical-align: top;"></td></tr></table>
Could there be another character instead of the space separating the words? I also tried alt32 and alt032 with no luck.

The spreadsheet originated in Thailand.

Thanks

 

[jimrward]

try in a spare cell try =code(MID(A1,5,1)) if your data is in A1, if it is a space you will get 32 in your spare cell, if not we move to plan B

 

[jimrward]

you can carefully copy with the mouse what appears to be a space, and then do DATA > TEXT to COLUMNS> tick other and then paste what you copied into the box

 

[Rasm]

try this - you can change the location yourself - I used cell A1 - the codes shown are the ASCII codes - so just look them up.

Public Sub asciiCharacters()
    Astr = Asc(Left(ActiveSheet.Range("A1"), 1))
    For i = 2 To Len(ActiveSheet.Range("A1"))
        Astr = Astr & "---" & Asc(Mid(ActiveSheet.Range("A1"), i, 1))
    Next i
    MsgBox ("ASCII values of each character=" & Astr)
End Sub

 

[Scott Huish]

Try ALT-0160

 

[mberow]

Thank you all for the quick replies, but nothing works.

I copy/pasted in the character from a cell, tried the MID trick, and copied in the VB script. I now know that the pesky character is an alt160, but the column still won't split. I tried with both alt0160 and alt160.

If you have anything else in the bag of tricks, I'd appreciate it.

Thanks again.