splitting cell text using macro
 

Hi Everybody

I have a spreadsheet that imports data from CSV file and although I can use the deliminate when importing, it does not work for all the values inported. For instance, some entries act as one word and are entered in the same cell ("AA") where in fact there should be two cells adjacent to each other with "A" in them both. It may also import showing A(A) where one cell should be A and the other (A).
I have tried the following formula below, and although it does copy the correct part of the text into the adjacent cell, it will still leave the first cell unaltered and hence I need to edit that cell and remove the text (and before that special paste the adjacent cells because of the formulas).
=IF(OR(A1="AA",A1="A(A)"),IF(A1="AA","A",IF(A1="A( A)","(A)")),"")

I am sure there is an easier automated way of achieving this, can anyone help?

Thank you

Garry

splitting cell text using macro
 

I would start by looking at the CSV file using notepad and try to determine
why the errors exist. the program that created the CSV file may be the
problem.

It is sometimes easier to read the CSV file as a text file with VBA and fix
the problems before the data gets put into a spreadsheet.

The CSV (comma seperated data) data should put each set of data between the
commas into its own cell in the spreadsheet.

"Garrystone" wrote:


Hi Everybody

I have a spreadsheet that imports data from CSV file and although I can
use the deliminate when importing, it does not work for all the values
inported. For instance, some entries act as one word and are entered in
the same cell ("AA") where in fact there should be two cells adjacent to
each other with "A" in them both. It may also import showing A(A) where
one cell should be A and the other (A).
I have tried the following formula below, and although it does copy the
correct part of the text into the adjacent cell, it will still leave the
first cell unaltered and hence I need to edit that cell and remove the
text (and before that special paste the adjacent cells because of the
formulas).
=IF(OR(A1="AA",A1="A(A)"),IF(A1="AA","A",IF(A1="A( A)","(A)")),"")

I am sure there is an easier automated way of achieving this, can
anyone help?

Thank you

Garry




--
Garrystone

Hi, Yes I understand where you are comming from, but I am not sure whether they are entered as one column at source instead of two individual columns. At my end I need to split them and hence would probably need a macro to automate the process.

Garry

splitting cell text using macro
 

The AA should be A,A and A(A) should be A,(A) - then it will work
correctly when importing CSV.

In VBA there is a handy Split() function that will separate data by a
delimiter, but it basically does the same thing as CSV import or Text-
to-Columns will. If your CSV file is properly formatted to begin
with, then you shouldn't be having this issue to begin with.



On Jan 31, 7:45 am, Garrystone
wrote:
Hi Everybody

I have a spreadsheet that imports data from CSV file and although I can
use the deliminate when importing, it does not work for all the values
inported. For instance, some entries act as one word and are entered in
the same cell ("AA") where in fact there should be two cells adjacent to
each other with "A" in them both. It may also import showing A(A) where
one cell should be A and the other (A).
I have tried the following formula below, and although it does copy the
correct part of the text into the adjacent cell, it will still leave the
first cell unaltered and hence I need to edit that cell and remove the
text (and before that special paste the adjacent cells because of the
formulas).
=IF(OR(A1="AA",A1="A(A)"),IF(A1="AA","A",IF(A1="A( A)","(A)")),"")

I am sure there is an easier automated way of achieving this, can
anyone help?

Thank you

Garry

--
Garrystone