• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to set excel default cell format as text

trprasad78

Member
Hi

Default excel cell format will be in "General"

because of that if i open .csv or .txt file zeros got remove automatically.

Example.
00000535
000000541

its turn to 535 and 541.

I know their is option in Text Column where i can choose Field type as "TEXT"

upload_2017-5-9_16-19-39.png

But I want to avoid this process , once we open it has to open as TEXT format.

so can we change the default text format from General to TEXT ?
OR
if you have any alternative way, where if we double click .CSV or .TXT file it has to open with proper format.

Please suggest.
 
But Changing Default to Text will not exact solution i guess, after change it to TEXT , if we open any data it will consider as text then i can't sum the number too.

If you have any alternative way the number has to open as 00000535
000000541
 
You can do this through VBA macro, if that is acceptable.
YES , That too fine.

Customer ID field will not be constant , I mean some time it will be in B column some time in J Column, but head row will be constant.

I have attached sample file.
 

Attachments

  • sample.txt
    857 bytes · Views: 9
Try this code

Code:
Sub read_whole_file()
  Dim sFile As String, sWhole As String
  Dim v As Variant, arr As Variant
  Dim x As Integer, y As Integer
  
  sFile = "C:\Users\Huis\Downloads\sample.txt" 'Change to suit
  Open sFile For Input As #1
  sWhole = Input$(LOF(1), 1)
  Close #1
  
  v = Split(sWhole, vbNewLine)
  Cells.ClearContents
  
  For x = 0 To UBound(v, 1)
  arr = Split(v(x), ",")
  
  With Sheet1
  For y = 0 To UBound(arr, 1)
  .Cells(x + 1, y + 1).NumberFormat = "@"
  .Cells(x + 1, y + 1).Value = arr(y)
  
  Next y
  End With
  Next x
  
  Sheet1.Columns("A:E").AutoFit
  
End Sub

Change the directory as highlighted above

upload_2017-5-9_21-51-53.png
 
Hi !
But I want to avoid this process
As it is the right way to specify format for each column,
if you want to avoid that you must ask for a smart csv file
aka with double quotes for data in text format ! No code needed …
 
Try this code

Code:
Sub read_whole_file()
  Dim sFile As String, sWhole As String
  Dim v As Variant, arr As Variant
  Dim x As Integer, y As Integer
 
  sFile = "C:\Users\Huis\Downloads\sample.txt" 'Change to suit
  Open sFile For Input As #1
  sWhole = Input$(LOF(1), 1)
  Close #1
 
  v = Split(sWhole, vbNewLine)
  Cells.ClearContents
 
  For x = 0 To UBound(v, 1)
  arr = Split(v(x), ",")
 
  With Sheet1
  For y = 0 To UBound(arr, 1)
  .Cells(x + 1, y + 1).NumberFormat = "@"
  .Cells(x + 1, y + 1).Value = arr(y)
 
  Next y
  End With
  Next x
 
  Sheet1.Columns("A:E").AutoFit
 
End Sub

Change the directory as highlighted above

View attachment 41540
Thank you working fine
 
Back
Top