How to save input the sheet name into a field in Excel

[Ringo_Wu]

I have excel files with many sheets.  I would like to add a column in a sheet showing the current sheet name.  How to do it in bulk?

Example as attached, I want to add a column that shows the sheet name e.g. "01 country"

Thank you.

[grossal]

Hey @Ringo_Wu,

here is a solution to your problem:

The result looks like this:

I have attached the sample workflow.

If this solved you problem, please consider to accept it as a solution or drop another question if it doesn't.

Alex

[Ringo_Wu]

Thanks, @grossal  for your reply.  Actually there is still a problem.  The two different sheets contain different values.  i.e. in sheet "02 Country ", there is a value of BC and DC, these values are not included in the output.  It seems only the value from sheet "01 country" is included and attached to the 2 sheet names accordingly.

In fact, I wish to have output as these in one sheet:

Number	city
1	AAA	01 Country
2	VV	01 Country
3	CX	01 Country
4	AZ	01 Country
Number	city
1	AAA	02 Country
2	BC	02 Country
3	CX	02 Country
4	DC	02 Country

[grossal]

Ohh sorry. I had a typo in the example.

This is the new result.

As an Excel User myself, I'd find it pretty odd to have another "header row" in the middle of the data. Usually you want to use Filters or Pivot Tables across the whole data and not only on the first N rows.

Can you explain why you want the additional header row?

Workflow attached. I also fixed the blank space in the end of the second work sheet because this was causing some trouble too.

Alex

[Ringo_Wu]

Hi Alex,

Thanks so much.  In fact, the sheet name in my file is an identifier of the data and I need to combine different sheets in one sheet with this identifier. 

I tried your workflow it worked.  However, when I use it to my actual file, it seems to work strangely.  The actual file has a different amount of data, say 4 in the first sheet and 6 in the second sheet.   Here is a screenshot of error when I changed the file in 02 country to 6 rows.  The result I am looking for is the 4 rows of data in 01 country is marked as 01 country following with the 02 county's 6 rows of data marked as 02 country.   

Number	city
1	AAA	01 country
2	VV	01 country
3	CX	01 country
4	AZ	01 country
1	AAA	02 country
2	BC	02 country
3	CX	02 country
4	DC	02 country
5	MS	02 country
6	TW	02 country
7	SZ	02 country

Thanks and wish you have a lovely weekends.

Ringo

[grossal · Accepted solution]

Hi @Ringo_Wu,

I downloaded your new Excel file, renamed and it worked as intended. See below.

(I think there is a little typo in your text, because your table shows my table, but your text says '6 rows' for file 2)

I think I know what caused the error. I forgot to use the relative path in the Input Tool, and only used it in the Dynamic Input. I'll attach the changed workflow.

If you aren't familiar with relative paths: This path indicates that the input file is in the same folder as the workflow file.

Feel free to change the path in both tools as needed. 

Happy weekend!

Alex

[Ringo_Wu]

Thanks a lot! It works now.

By the way, I found another reason for why the previous time it didn't work.  I miss the "$" sign in the setting to replace a specific string "sheet name$".

Now it worked all well, a million thanks!