In this String "AB0610E001" I am using "Str.Substring(Str.Length -6)" this expression to get the last 6 digits of the String like this "10E001" but meanwhile i am entering it into excel cell i am getting like number value "100"

[Gopikrishna_S]

Please suggest a correct method to over come this, I need Output like this 10E001 I dont want 100

[william.coulson (Will Coulson)]

Hi @Gopikrishna_S,

Have you tried adding an apostrophe to your value when writing it?

"'" + YourVar

[jack.chan]

another solution:

1. highlight column in excel
   
   
2. click data-> text to column
   
   

.3 select TEXT and click FINISH

4. now when you write to this cell, it will show 10E001

[Gopikrishna_S]

How to Apply text format using bot

[jack.chan]

then do what @william.coulson said above

In this String "AB0610E001" I am using "Str.Substring(Str.Length -6)" this expression to get the last 6 digits of the String like this "10E001" but meanwhile i am entering it into excel cell i am getting like number value "100"

Hi @Gopikrishna_S, Have you tried adding an apostrophe to your value when writing it? "'" + YourVar

[Gopikrishna_S]

I am going convert this excel data into text format, that time i would get appostopy, instead of appostopy I need another method.

[jack.chan]

here

1. create txt file VBAFunction.txt

paste this inside

Function ChangeFormatToText(sheetName As String)
    
    Dim ws As Worksheet
    Set ws = ActiveWorkbook.Sheets(sheetName)
    ws.Activate
    Dim rLastCell As Range
    Set rLastCell = ws.Cells.Find(What:="*", After:=ws.Cells(1, 1), LookIn:=xlFormulas, LookAt:= _
    xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False)

    Dim lastColumnNumber as Integer
    if rLastCell is nothing then
        lastColumnNumber = 100 
    else
        lastColumnNumber = rLastCell.Column
    end if
    
 

    Rows(1).Insert
    Range(Cells(1, 1), Cells(1, lastColumnNumber)).Value = "test"
    
    
    For i = 1 To lastColumnNumber
    

    Columns(i).Select
    
    Selection.TextToColumns Destination:=Cells(1, i), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 2), TrailingMinusNumbers:=True
    Selection.NumberFormat = "@"
    Next i
    
    Rows(1).Delete
    ActiveWorkbook.Save
End Function

Then in excel application scope, call vba function using invoke vba, in EntryMethodParamters pass in your sheet name e.g. {“Sheet1”}

now if you write cell it will be text format

[Gopikrishna_S]

@jack.chan

VBAFunction

is this Code Convert the whole Sheet1 of the excel or it will convert a particular Column into text format

[jack.chan]

whole sheet (up to last column), or do you only want 1 column?

[Gopikrishna_S]

Meanwhile running this i am getting this

[jack.chan]

open any excel, go to file->options

follow my settings here and click OK

[Gopikrishna_S]

Provide a Code for Single COlumn

[jack.chan]

this
then when calling you need to add sheet name parameter and column letter
e.g. {“Sheet1”,“C”} for column C and sheet1

Function ChangeFormatToText(sheetName As String, colLetter As String)
    
    Dim ws As Worksheet
    Set ws = ActiveWorkbook.Sheets(sheetName)
    ws.Activate
 
    Rows(1).Insert
    Range(colLetter & "1").Value = "test"
    
    Dim columnNumber As Integer
    columnNumber = Range(colLetter & "1").Column

    Columns(columnNumber).Select
    
    Selection.TextToColumns Destination:=Cells(1, columnNumber), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 2), TrailingMinusNumbers:=True
    Selection.NumberFormat = "@"
    
    Rows(1).Delete
    ActiveWorkbook.Save
End Function

[Gopikrishna_S]

if i want to Change the Column name from C to A means, If it is enough to change Column name in Parameter

[jack.chan]

yes you can change however you like .

[jack.chan]

if you want to change for columns A,B just call it twice

[Gopikrishna_S]

Thank you Jack.chan Its Working Fine

[Gopikrishna_S]

How to Convert into text format in .csv file