Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 Quote:
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Splitting text in a cell | Excel Worksheet Functions | |||
Splitting text in a cell | Excel Discussion (Misc queries) | |||
splitting text from one cell | Excel Discussion (Misc queries) | |||
splitting text in a cell.. | Excel Discussion (Misc queries) | |||
Splitting Up Text in One Cell To Many | Excel Worksheet Functions |