text to columns

[David]

i am trying to text the data into two columns (delimited by space) but can
only display the names in a new column line but not the rest number. The data
are all in one cell but with a square box after the name. I guess it is the
Return key symbol. How I can remove it or text the data into two. I need to
seperate the name and the #. Thanks,

 

[teylyn]

Hi,

first you may need to identify the character that appears as a square
Try to copy that character and paste it into a cell, e.g. A1 on
different sheet, and then in B1 put the formula

=CODE(A1)

Now you see a number. Use this number in the formula back in your list

=Substitute(A1,Char(<number>)," ")

to replace the character in A1 with a blank. Adjust the formula t
reflect your data range and the replacemet character, then copy dow
your list of text cells.

After that, you can copy the result and use paste special values t
create text for Text to Columns again.

hth

 

[David]

I found the code for the return key is 10 and your formula works. I can text
the whole column now.
Thanks,

Hi,

first you may need to identify the character that appears as a square.
Try to copy that character and paste it into a cell, e.g. A1 on a
different sheet, and then in B1 put the formula

=CODE(A1)

Now you see a number. Use this number in the formula back in your list

=Substitute(A1,Char(<number>)," ")

to replace the character in A1 with a blank. Adjust the formula to
reflect your data range and the replacemet character, then copy down
your list of text cells.

After that, you can copy the result and use paste special values to
create text for Text to Columns again.

hth

i am trying to text the data into two columns (delimited by space) but
can
only display the names in a new column line but not the rest number.
The data
are all in one cell but with a square box after the name. I guess it is
the
Return key symbol. How I can remove it or text the data into two. I
need to
seperate the name and the #. Thanks,

--
teylyn

Telyn -- 'teylyn.posterous.com' (http://teylyn.posterous.com)
------------------------------------------------------------------------
teylyn's Profile: 983
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=162840

Microsoft Office Help

.