This is a Rube Goldberg way to do this, but I've found it to be pretty effective.

Copy the entire spreadsheet and paste into a basic text editor like notepad.

Then copy the text from notepad into MS Word.

The first step is to replace all paragraph breaks with a non-numeric character but also one unlikely to appear in your data. I find that '|' is usually safe. Launch the search and replace box, enter ^p as the search string, and enter | as the replacement. Make sure you enable highlight formatting. Hit replace all. MS Word will convert all line breaks into the highlighted pipe character.

Next, you need to isolate the numbers. Open the search and replace tool. With the wildcards option enabled, enter the following as your search string: [0-9] . Set the replace option to be the 'find what text option' which is expressed as ^& . Enable formatting, and choose to make it highlighted. Then hit replace all. You will then see anything with a number now treated with a visual highlight.

Now it's time to get rid of the data you don't care about. From search and replace, you want to remove any character that is not highlighted. Choose '?' as your search string, choose 'not highlighted' from your format option, and enable wildcards. For replace, leave it blank. Hit replace all. You will then be left with only your numbers and pipes, all highlighted.

The last step is to reconvert the pipes into carriage returns. Search for '|' and replace with '^p'. You will then be left with your accounts, a carriage return reflecting each seperate record.

Copy the above, paste into notepad, then paste into a column in MS excel. Voila.

This method isn't great if your data includes numeric characters that you don't want. (numbers that aren't part of the account string)

You can write a VB function that does the above or use the macro recorder if you need to run this multiple times.

Again, definitely not elegant but works. I've found the wildcard tool in MS to be very robust, provided you are willing to get creative.

Option Explicit

Sub GLLoadDataGenerator()

'Write GL Test Data to a tab delimited .TXT file

Dim NumberofRecords As Variant, i As Variant
Dim myPath As String, myFile As String, DateTime As String, FolderName As String
Dim iTEXT As Integer
Dim sTEXT As String 'csv string to print
Dim sDelimiter As String
Dim FileNum As Integer
Dim fso
Dim varglcode As Variant, strgltext As String, varbalance As Variant, strgltypecode As Variant, varfundcode As Variant, varclasscode As Variant, dtloaddate As Date

Randomize

sDelimiter = vbTab
varglcode = 100000
varbalance = 1000000
NumberofRecords = 50000

'DateTime = Format(Date, "dd-mm-yyyy") + "_" + Format(Time, "hh-mm-ss AM/PM")
FileNum = FreeFile
myPath = CreateObject("WScript.Shell").SpecialFolders("DeskTop") & "\TestLoadData\"
'myFile = "GLLoadTestData_" & NumberofRecords & "_" & DateTime & ".txt"
myFile = "GLLoadTestData_" & NumberofRecords & " Records" & ".txt"
Set fso = CreateObject("Scripting.FileSystemObject")

If Not fso.FolderExists(myPath) Then
fso.CreateFolder (myPath)
End If

Open myPath & myFile For Output Access Write As #FileNum
'Print the header row
Print #FileNum, "glcode" & sDelimiter & "gltext" & sDelimiter & "balance" & sDelimiter & "gltypecode" & sDelimiter & "fundcode" & sDelimiter & "classcode" & sDelimiter & "loaddate"

For i = 1 To NumberofRecords
DoEvents
'Generate the GL Account Code
varglcode = varglcode + 1
sTEXT = sTEXT & varglcode & sDelimiter

'Generate the GL Account Text
strgltext = "GL Load Text_" & varglcode
sTEXT = sTEXT & strgltext & sDelimiter

'Generate GL Balance
varbalance = varbalance + 1
sTEXT = sTEXT & varbalance & sDelimiter

'Generate GL Type Code
'RandomNumber = Int((5 * Rnd) + 1)
Select Case Int((5 * Rnd) + 1)
Case 1
strgltypecode = "A"
Case 2
strgltypecode = "C"
Case 3
strgltypecode = "E"
Case 4
strgltypecode = "I"
Case 5
strgltypecode = "L"
End Select

sTEXT = sTEXT & strgltypecode & sDelimiter

'Generate Fund Code and Clase Load Code
Select Case Int((4 * Rnd) + 1)
Case 1
varfundcode = "FD01"
varclasscode = "FD01_Load_a"
Case 2
varfundcode = "FD01"
varclasscode = "FD01_Load_b"
Case 3
varfundcode = "FD02"
varclasscode = "FD02_Load_a"
Case 4
varfundcode = "FD02"
varclasscode = "FD02_Load_b"
End Select
sTEXT = sTEXT & varfundcode & sDelimiter & varclasscode & sDelimiter

'Generate Load Date
dtloaddate = "4/19/2009"
sTEXT = sTEXT & dtloaddate

Print #FileNum, sTEXT
sTEXT = ""
Next

Close #FileNum

' If Err <> 0 Then
' ErrorMessageCount = ErrorMessageCount + 1
' ReDim Preserve ErrorMessage(1 To ErrorMessageCount)
' ErrorMessage(ErrorMessageCount) = "--There was an error saving the Bug Report to file."
' Err.Clear
' GoTo ResultMessages
' End If

End Sub