CSV Column Conversion

  • Thread starter Thread starter CJ Melo
  • Start date Start date
C

CJ Melo

Hi
I have a downloaded csv file which has a LastName,FirstName entry under one
column titled LearnerName. I am trying to highlight rows based on the names,
but conditional formatting doesn't seem to like the comma in the name. Short
of manually breaking the name into two columns, is there a way to convert
LastName,FirstName into two separate columns?
 
I'd use:

Select the column with the names
Data|Text to columns
Delimited
by comma
and plop the results into the two adjacent columns.

Make sure you have room so you don't overwrite the adjacent data.
 
By "manually", do you mean editing them one at a time? You can use Text to
Columns to separate them into two columns all in one operation. Select all
the combined names in the LearnerName column. Then select Data >> Text to
Columns >> Delimited >> Next >> Comma >> Next >> Finish.

Another way is to use formulas to parse the names into two other columns. If
the first combined name is in A2, for example, enter this formula in B2:
=TRIM(LEFT(A2,FIND(",",A2)-1)

and this formula in C2:
=TRIM(RIGHT(A2,LEN(A2)-FIND(",",A2)))

Copy both formulas down through all rows of data, then copy & paste in place
as values.

Hope this helps,

Hutch
 
Back
Top