Separate text in a single cell into multiple columns - Writing a macro

sagita

New Member
Joined
Mar 21, 2010
Messages
1
Hi All,

I have a *.lis extension file opened in excel. The text is in a single cell and I wish to separate the information into separate columns. Part of the text is delimited by "|" but some isn't.

It is a GL output which someone runs everyday, I wish to write a macro to separate the info into different columns and allow me to use pivot table to manipulate it. Text to columns doesn't work since the width varies from line to line.

*Example*
| True Account | Opening Balance | Net Movement | Closing Balance | <-- Heading
| Inter Ledger Clearing Accounts... (12234 | 11.00 | 5.00 | 16.00 | <--- Line 1
| Clearing Account...... (12277 | 33.00 | 1.00 | 34.00 | <--- Line 2
| IRS Account Interest Income...... (14477 | 1,000.00 | 1.00 | 1,001.00 | <--- Line 3

I wish to separate it into 5 different columns

*Line 1 Target Example*
1) True Account [Inter Ledger Clearing Accounts] 2) True Account Number* [12234] 3) Opening Balance [11.00] 4) Net Movement [5.00] 5) Closing Balance [16.00]

*New column I wish to write a macro to add this column in.

Can someone please assist me in this? Hope I'm clear on what's required. If not please let me know. Thanks a mill!
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

snb_

Well-known Member
Joined
Nov 9, 2009
Messages
567
Code:
Sub tst()
  Sheets(1).Columns(1).TextToColumns , 1, -4142, , False, False, False, False, True, "|"
  Sheets(1).Columns(1).TextToColumns , 1, -4142, , False, False, False, False, True, "("
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,099,794
Messages
5,470,821
Members
406,727
Latest member
Kimze

This Week's Hot Topics

Top