How to Write data in Excel as text

[Anatoliy1992]

Hi All,

Sorry that might be a stupid question but i haven't find anything about that in google.

I have very silly issue but cant solve it.

i have product code looks like that 0000005 but when i read data and write to another workbook excel automatically convert data to 5. even if the data as text. please see screenshoots.

I really need this any help will be good

 

  

[MichaelAnnis]

Have you tried adding an apostrophe in front....ex '000005 

The apostrophe tells Excel that it is text.  It exists in the "text value" but disappears on display.  Good luck!

[Guriga]

Is the other Excel spreadsheet already saved?

If yes, format the specific cell as text instead of general.

[Anatoliy1992]

If im using find and replace it takes forever to replace all zeros it takes forever. is there any quick way to add an apostrophe in front and make it in flow? 

[Anatoliy1992]

not sure what you mean? 

[Guriga]

The file you write the data into it it is created by the flow or already saved somewhere?

[Anatoliy1992]

The file already saved. the thing is that i have to edit about 37k cells it takes forever for PAD. 

[VJR]

Hi @Anatoliy1992 

Since you have those many number of rows and also that PAD does not yet have any inbuilt action for formatting cells you will need to use something like a script.

If you are fine to use a vbscript then take a look at my post here.

Clicking the above link will directly take you to the post.

The only change that you need to do is:

- From the vbscript, remove code line numbers 7 to 11
  and instead write
  objExcel.Range("%FromRange%" & ":" & "%ToRange%").NumberFormat = "000000"

In the above line modify the number of 0's as per your requirement.

Please try this on a backup file before doing it on the original Excel.

[Guriga]

You misunderstand me. 

Open the saved Excel file. Select the column and change it from General to Text and save the file again.

So when the PAD will write into that cells it will be text and keep the 00000 in the front.

[Anatoliy1992]

I have changed data import, now it import data by power query and i can set up any type of fields. 

Thank you all for your help! 

[HazemSaleh]

Im using this but always show in excel !! 
Is iam missing something?