How do we convert number to text format. On 21-May-2011 11:06 AM, "ashish koul" <koul.ash...@gmail.com> wrote:
Sub delimit_txt_file() Dim s As Workbook Dim fNameAndPath As Variant fNameAndPath = Application.GetOpenFilename _ (FileFilter:="Text Files (*.txt), *.txt", _ Title:="Select File To Be Opened") Workbooks.OpenText Filename:=fNameAndPath, Origin:=xlWindows, _ StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _ , Space:=False, Other:=True, OtherChar:="|" Set s = ActiveWorkbook s.Sheets(1).Cells.Select s.Sheets(1).Cells.EntireColumn.AutoFit Dim i As Long For i = 2 To s.Sheets(1).Range("a1").End(xlDown).Row s.Sheets(1).Cells(i, 3).Value = Application.Trim(s.Sheets(1).Cells(i, 3).Value & "|" & s.Sheets(1).Cells(i, 4).Value) If Right(s.Sheets(1).Cells(i, 3).Value, 1) = "|" Then s.Sheets(1).Cells(i, 3).Value = Left(s.Sheets(1).Cells(i, 3).Value, Len(s.Sheets(1).Cells(i, 3).Value) - 1) End If If Left(s.Sheets(1).Cells(i, 3).Value, 1) = "|" Then s.Sheets(1).Cells(i, 3).Value = Right(s.Sheets(1).Cells(i, 3).Value, Len(s.Sheets(1).Cells(i, 3).Value) - 1) End If Next i s.Sheets(1).Columns("D:D").Delete Shift:=xlToLeft End Sub On Thu, May 19, 2011 at 8:59 PM, Susan 1 <sunni...@gmail.com> wrote: > Hi Jsin - > I've attached an example of the formatted spreadsheet and the raw data txt > file, the macro or VB I need would automatically format the txt file into > the spreadsheet style. > > Thanks for you help on this! > > On Thu, May 19, 2011 at 4:54 AM, JsinSk <jsin...@gmail.com> wrote: > >> I can guess how you want it formatted, but is it possible for you to >> attach a sample excel sheet with the desired tabs/columns how you want >> it? >> >> On May 18, 3:31 pm, Susan 1 <sunni...@gmail.com> wrote: >> > Hi ~ >> > >> > Wondering if you could assist me with the Text import wizard to >> configure >> > the columns and tab delimiter for the attached txt file. And then send >> me >> > the steps. >> > >> > I will be needing to do this every week to the same types of txt files, >> > importing into Excel and I’d like to create a Macro for this but cannot >> > figure out how to set the tab/columns. >> > >> > Thanks, >> > >> > Sunnie >> > >> > 2065409868_05-03-2011.txt >> > 6KViewDownload >> >> -- >> >> ---------------------------------------------------------------------------------- >> Some important links for excel users: >> 1. Follow us on TWITTER for tips tricks and links : >> http://twitter.com/exceldailytip >> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 >> 3. Excel tutorials at http://www.excel-macros.blogspot.com >> 4. Learn VBA Macros at http://www.quickvba.blogspot.com >> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >> >> To post to this group, send email to excel-macros@googlegroups.com >> >> <><><><><><><><><><><><><><><><><><><><><><> >> Like our page on facebook , Just follow below link >> http://www.facebook.com/discussexcel >> > > -- > > ---------------------------------------------------------------------------------- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below link > http://www.facebook.com/discussexcel > -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com <http://akoul.blogspot.com/> *akoul*.wordpress.com <http://akoul.wordpress.com/> My Linkedin Profile <http://in.linkedin.com/pub/ashish-koul/10/400/830> P Before printing, think about the environment. -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel