How to Write data in Excel as text

(0) Share

Report

Posted on by Alfi_al

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

Categories:

Power Automate Desktop

All responses (10)

Answers (0)

Sort by

MichaelAnnis 5,727 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!

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 188 on at

Like
a
(0)

Report
Copy link

Link copied!

Is the other Excel spreadsheet already saved?
If yes, format the specific cell as text instead of general.
Alfi_al 14 on at

Like
a
(0)

Report
Copy link

Link copied!

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?
Alfi_al 14 on at

Like
a
(0)

Report
Copy link

Link copied!

not sure what you mean?
Guriga 188 on at

Like
a
(0)

Report
Copy link

Link copied!

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

Like
a
(0)

Report
Copy link

Link copied!

The file already saved. the thing is that i have to edit about 37k cells it takes forever for PAD.
VJR 7,635 on at

Like
a
(0)

Report
Copy link

Link copied!

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 188 on at

Like
a
(0)

Report
Copy link

Link copied!

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.
Alfi_al 14 on at

Like
a
(1)

Report
Copy link

Link copied!

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 29 on at

Like
a
(0)

Report
Copy link

Link copied!

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