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

[sagita]

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!

 

[hiker95]

sagita,

Welcome to the MrExcel board.

Can we see a screenshot of your actual data in your worksheet?

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4 (contains graphic instructions).
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

 

[maxmadx]

I think your problem was resolved in an earlier thread here:

http://www.mrexcel.com/forum/showthread.php?t=451839&highlight=true+letters

 

[snb_]

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