why won't text-to-columns work here?

  • Thread starter Thread starter Laurence Smith
  • Start date Start date
L

Laurence Smith

I downloaded a CSV file from the net and converted it to an Excel file
(I use Excel 2002 on my Vista PC).

The A column has names like:

John Doe
Joe Smith*
Harry Jones#

etc.

The text to column thing does not work and I've tried lots of things
like copying the column to Notepad and recopying it back in so as to
make sure Excel is seeing this as text.
I've done the same and used the REPLACE function in Notepad to take
away the asterisks and pound signs. Still no go.

Is there any special trick to getting the text-to-column thing to work.
I want column A to have all the first names and column B to have all
the last names.

I also used this formula only to get the #Value error:
=RIGHT(A1,LEN(A1)-FIND(" ",A1))&", "&" "&LEFT(A1,FIND(" ",A1)-1)


Thanks in advance for any help.
 
Hi,

Am Thu, 9 Oct 2014 16:57:07 +0000 (UTC) schrieb Laurence Smith:
John Doe
Joe Smith*
Harry Jones#
Click to expand...

with your example the formula works and TextToColumns with space as
separator works too.
You can also use:
=MID(A1&", "&A1,FIND(" ",A1)+1,LEN(A1)+1)


Regards
Claus B.
 
Claus said:
=MID(A1&", "&A1,FIND(" ",A1)+1,LEN(A1)+1)
Click to expand...

Thank you for trying but I plugged in the formula and got only #Value!
in every cell.

There is something about having downloaded the CSV file that makes the
text in my column A unable to be manipulated.

I am sure that if I typed in the names individually that the formula
and text to column would work. It just won't work on these imported
names.
 
Hi Laurence,

Am Thu, 9 Oct 2014 18:33:01 +0000 (UTC) schrieb Laurence Smith:
There is something about having downloaded the CSV file that makes the
text in my column A unable to be manipulated.
Click to expand...

if John Doe is in A1 then test for the sign between first and last name:
=CODE(MID(A1,5,1))
If it is not 32 then change this sign to chr(32)


Regards
Claus B.
 
Laurence Smith said:
I downloaded a CSV file from the net and converted it to
an Excel file (I use Excel 2002 on my Vista PC).
The A column has names like:
John Doe
Joe Smith*
Harry Jones# [....]
The text to column thing does not work [....]
I also used this formula only to get the #Value error:
=RIGHT(A1,LEN(A1)-FIND(" ",A1))&", "&" "&LEFT(A1,FIND(" ",A1)-1)
Click to expand...

The fact that FIND(" ",A1) returns a #VALUE error demonstrates that what
looks like a space is not a space. It might be a tab character; but more
likely, it is a nonbreaking space (HTML nbsp), copied from a web page.

In a parallel column, you can use =SUBSTITUTE(A1,CHAR(160)," ") to replace
nonbreaking spaces with spaces.

Alternatively, you can make Text To Columns work by very carefully
highlighting one of the "spaces" in the Formula Bar, then in step 2 of TTC,
select the delimiters Tab, Space, and Other, pasting the copy "space" into
the field next to Other. Be sure that "Treat consecutive delimiters as one"
is also selected.
 
PS.... I said:
Alternatively, you can make Text To Columns work by very carefully
highlighting one of the "spaces" in the Formula Bar
Click to expand...

Of course, you be sure to highlight one of the "spaces" that is not a normal
space. Hard to tell.

Alternatively and more reliably.... In an unused cell, enter the formula
=CHAR(160). Select that cell, put the cursor at the end in the Formula Bar,
press F9 to replace the formula with its value in the FB, press ctrl+C to
copy, then press Esc to restore the formula.

Paste the copied nonbreaking space into the field next to Other in step 2 of
the Text To Columns operation.
 
joeu2004 said:
=SUBSTITUTE(A1,CHAR(160)," ")
Click to expand...

First. Thank you Claus and Joe.
The code thing didn't work. It seemed to not even process since the
cell read exactly =CODE(MID(A2,5,1)

But the substitute thing worked like a charm and now I have been able
to put the names in just the form I need.

Very much appreciated.
 
Back
Top