Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delimiting across multiple columns
Hi
I am interested if a macro exists that will delimit across multiple columns in Excel. My data is as follows and I wish to split the antibiotic from the letter which is 23 characters from the beginning of the column. An example of the data is as follows, I am unsure how to put borders in but the first example Amoxicillin and R is in same cell in Excel, R is 23 Characters from the beginning of the cell. On conversion Amoxicillin and R are in two seperate cells. Amoxicillin R Vancomycin R Teicoplanin R Amoxicillin R Nalidixic acid s Teicoplanin s Amoxicillin R Vancomycin s Teicoplanin s Amoxicillin R Vancomycin R Teicoplanin S Amoxicillin S Amoxicillin R Vancomycin S Nitrofurantoin S Amoxicillin R Vancomycin s Teicoplanin s Nitrofurantoin S Vancomycin s Amoxicillin S Vancomycin s Teicoplanin s Convert to Amoxicillin R Vancomycin R Teicoplanin R Amoxicillin R Nalidixic acid s Teicoplanin s Amoxicillin R Vancomycin s Teicoplanin s Amoxicillin R Vancomycin R Teicoplanin S Amoxicillin S Amoxicillin R Vancomycin S Nitrofurantoin S Amoxicillin R Vancomycin s Teicoplanin s Nitrofurantoin S Vancomycin s Amoxicillin S Vancomycin s Teicoplanin s I hope this is clear Many thanks Eddie |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delimiting across multiple columns
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delimiting across multiple columns
On Tuesday, December 10, 2013 4:33:44 PM UTC, Claus Busch wrote:
Hi, Am Tue, 10 Dec 2013 07:24:28 -0800 (PST) schrieb : Amoxicillin R Vancomycin R Teicoplanin R Amoxicillin R Nalidixic acid s Teicoplanin s Amoxicillin R Vancomycin s Teicoplanin s Amoxicillin R Vancomycin R Teicoplanin S Amoxicillin S Amoxicillin R Vancomycin S Nitrofurantoin S Amoxicillin R Vancomycin s Teicoplanin s Nitrofurantoin S Vancomycin s Amoxicillin S Vancomycin s Teicoplanin s try: Sub SplitString() Dim LCol As Integer Dim i As Integer With ActiveSheet LCol = .Cells(1, .Columns.Count).End(xlToLeft).Column For i = 2 To LCol * 2 Step 2 .Columns(i).Insert .Columns(i - 1).TextToColumns Destination:=.Cells(1, i - 1), _ DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False, _ Comma:=False, Space:=True, Other:=False, FieldInfo _ :=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True Next End With End Sub Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 Hi Claus Thanks for this unfortunately it splits column A over 2 columns but but not original columns B and C. I showed 3 columns of data but sometimes there are up to 20 columns that need to be split if this makes a difference. Eddie |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delimiting across multiple columns
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
problem with test to columns feature identifying odd delimiting character | Excel Discussion (Misc queries) | |||
Un-Delimiting | Excel Programming | |||
Delimiting and Formatting | Excel Discussion (Misc queries) | |||
delimiting w/multiple delimiters? | Excel Discussion (Misc queries) | |||
Pasting from clipboard-how to control text-to-columns delimiting | Excel Discussion (Misc queries) |