How To Summ A Cloumn In Txt Format

  • Quote from royUK

    Do you mean that you want to convert your numbers to words eg


    100 becomes one hundred


    no, simple in F282 =SOMMA(F3:F281)


    or another way...
    i import this data from a txt file from this line
    duruing the import if is possible to transorm the string in the number format and put this in the relative cells...
    Sub IMPAGATI()


    Sheets("IMPAGATI").Select


    Sheets("serv").Unprotect password:="SAL21"


    If Sheets("serv").Range("z1") = "" Then
    Call Chop132
    Sheets("serv").Range("z1") = "X"
    End If


    Sheets("serv").PROTECT DrawingObjects:=True, contents:=True, Scenarios:=True, password:="SAL21"


    Set Elenco = Worksheets("IMPAGATI")
    n = FirstFree("IMPAGATI", "A", 2) 'Record iniziale per il riempimento del tabulato
    PGM = False


    'Comando inibito per l'azzeramento dei dati importati


    iFile = FreeFile()
    NomeFile = "C:\EPF\PORTAF_132.EPF"


    'Ciclo apertura file di testo



    Open NomeFile For Input As #iFile
    Do Until EOF(iFile)
    Line Input #iFile, Record_Corrente$
    If Mid(Record_Corrente, 3, 11) = "PGM. VLG77B" Then
    PGM = True
    ElseIf Mid(Record_Corrente, 3, 5) = "PGM. " And Mid(Record_Corrente, 8, 7) <> "VLZ800B" Then
    PGM = False
    End If
    If PGM = True Then
    If Mid(Record_Corrente, 3, 9) = "SPORTELLO" Then
    Sportello_Corrente = Mid(Record_Corrente, 20, 5)
    End If
    If Mid(Record_Corrente, 3, 7) = "PARTITA" Then
    Partita_Corrente = Val(Mid(Record_Corrente, 20, 15))
    Creditore_Corrente = Trim(Mid(Record_Corrente, 38, 50))
    End If
    If Mid(Record_Corrente, 3, 15) = "PRODOTTO :" Then
    Prodotto_Corrente = Trim(Mid(Record_Corrente, 18, 7))
    TipoProdotto_Corrente = Trim(Mid(Record_Corrente, 38, 50))
    ModUtiliz_Corrente = Trim(Mid(Record_Corrente, 106, 4))
    DescrUtiliz_Corrente = Trim(Mid(Record_Corrente, 113, 20))
    End If
    If Mid(Record_Corrente, 3, 15) = "C/ADDEBITO :" Then
    AgenAdd_Corrente = Val(Mid(Record_Corrente, 20, 5))
    ContoAdd_Corrente = Val(Mid(Record_Corrente, 26, 12))
    TotAdd_Corrente = Trim(Mid(Record_Corrente, 74, 21))
    End If
    If Mid(Record_Corrente, 3, 15) = "OPERAZ. N. :" Then
    Operaz_Corrente = Mid(Record_Corrente, 18, 17)
    End If

    If Mid(Record_Corrente, 19, 1) = "/" Then
    'Intercettazione Variabili record corrente
    N_Effetto = Trim(Mid(Record_Corrente, 6, 10))
    Scadenza = DateValue(Mid(Record_Corrente, 17, 10))
    Importo = Trim(Mid(Record_Corrente, 27, 14))
    Data_Scadenza = Mid(Record_Corrente, 53, 2)
    Debitore = Trim(Mid(Record_Corrente, 109, 13))
    Causale = Mid(Record_Corrente, 123, 10)
    'Motivazione_Debitore = Trim(Mid(Record_Corrente, 94, 50))

    'Scrittura record su elenco

    Sheets("IMPAGATI").Unprotect password:="SAL21"

    Elenco.Range("A" + CStr(n)).Value = Partita_Corrente
    Elenco.Range("B" + CStr(n)).Value = Sportello_Corrente
    Elenco.Range("C" + CStr(n)).Value = Creditore_Corrente
    Elenco.Range("D" + CStr(n)).Value = N_Effetto
    Elenco.Range("E3:E65536").NumberFormat = "DD/MM/YYYY"
    Elenco.Range("E" + CStr(n)).Value = Scadenza
    Elenco.Range("F" + CStr(n)).Value = Importo
    Elenco.Range("G" + CStr(n)).Value = Debitore
    Elenco.Range("H" + CStr(n)).Value = AgenAdd_Corrente
    Elenco.Range("I" + CStr(n)).Value = ContoAdd_Corrente
    Elenco.Range("J" + CStr(n)).Value = Prodotto_Corrente
    Elenco.Range("K" + CStr(n)).Value = TipoProdotto_Corrente
    Elenco.Range("L" + CStr(n)).Value = Causale
    n = n + 1
    End If
    End If
    Loop

    Close #iFile ' Chiusura Handle file di testo


    Call CONTA
    Call ORDINADUP
    Call DUPLICATI
    Call ORDINAIMPAG
    Call SOST2


    ActiveWindow.SmallScroll ToRight:=-10


    Range("A3").Select


    'MsgBox ("ELEBORAZIONE TERMINATA!")


    Sheets("IMPAGATI").PROTECT DrawingObjects:=True, contents:=True, Scenarios:=True, password:="SAL21"


    End Sub

  • ... only the first 5 charachetr in txt format


    this sheet contain a set of date in line 2, i would wont to transform this date in txt value and get only the first 5 character...
    for example 01/01/2004 in 01/01

  • Hello,


    I have uploaded your file with an example.


    1 solution just with a formule and another with a macro(VBa). I 'm not sure how you want to do it actually.


    Vba:
    CSTR() converting to string
    Left() taking characters from the left
    format() to format the date(being sure that the first 5 char. are day and month)


    => CStr(Left(Format(ActiveSheet.Range("A2").Value, "dd/mm/yyyy"), 5))


    Is this what you want?


    Gollem


  • Tks. I have choice the formula, but if i drag to the right the formula in A4 from all range i recive in all cell where the formula is dragged?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!