Displaying Excel data in forum posts

[tescor]

Hi there,

Please can someone advise if I can easily convert Excel data into a table layout in a PH forum post?

Formatting Help shows this for a table

->||=Heading=||=Heading 2=||
->|| Cell1 || Cell2 ||

Heading	Heading 2
Cell1	Cell2

Is there a simple way of getting my Excel data into that format please?

[bigpriest]

If you had three columns of data in A,B and C, then in D you'd enter a formula like:

="||="&A1&"=||"&B1&"=||"&C1&"=||"

Then copy the formula down column D.
Copy and paste the results from column D into your post.

[tescor]

bigpriest said:

If you had three columns of data in A,B and C, then in D you'd enter a formula like:

="||="&A1&"=||"&B1&"=||"&C1&"=||"

Then copy the formula down column D.
Copy and paste the results from column D into your post.

Thanks, I can do that, but it's quite a manual job to do that each time, and for each column. I'll give it a go and see how much work is involved.

I was hoping for a website that would let me paste in my copied cells from Excel, and produce the correct format

[Crafty_]

You could also cut & paste the data in to notepad++, start recording a macro and edit the first line to look correct, move to send line, stop recording and then replay the macro to end of file

https://notepad-plus-plus.org/

https://npp-user-manual.org/docs/macros/

Will take you maybe 30 seconds.

Edited by Crafty_ on Tuesday 24th December 15:06

[bigpriest]

tescor said:

bigpriest said:

If you had three columns of data in A,B and C, then in D you'd enter a formula like:

="||="&A1&"=||"&B1&"=||"&C1&"=||"

Then copy the formula down column D.
Copy and paste the results from column D into your post.

Thanks, I can do that, but it's quite a manual job to do that each time, and for each column. I'll give it a go and see how much work is involved.

I was hoping for a website that would let me paste in my copied cells from Excel, and produce the correct format

Ah, right, lots of columns? You could mess around with this formula:

=SUBSTITUTE(ADDRESS(ROW(),COLUMN(),4),"1","")

Placed in a cell it returns the Column Letter which you could use in conjunction with the above formula to cut down the amount of manual typing. You'd be OK if you had a known number of data columns.

I've had too many Snowballs to think it through.

[Nigel_O]

Do you want PH readers to be able to interact with the table? If not, simply take a partial screenshot of the Excel worksheet and paste it into the PH post as an image.

[White-Noise]

I'd try putting your data into an ai and let it do it for you. I would think you can get it to do that. Unless this is something you need to do very regularly?

[MesoForm]

If it’s not something you’re going to do very often you could copy and paste (as plain text) into Word then do find & replace to replace tab (from memory the special characters are in a drop down) with ||

Edited by MesoForm on Thursday 26th December 23:06

[Craikeybaby]

White-Noise said:

I'd try putting your data into an ai and let it do it for you. I would think you can get it to do that. Unless this is something you need to do very regularly?

This is what I would do, tell it what format you need it in, then copy the data in.

[jagnet]

A bit late to the party as I was on holiday at the time and been busy since.

Anyway, it bugs me that there isn't a straight forward solution to this. So I made one.

Phables: phables.co.uk

It'll accept CSV, XLS, XLSX, and ODS files. Or copy and paste your comma separated CSV. Then just hit 'copy to clipboard'.

Some sample CSV data if anyone wants to try it:

Name,Age,Weight (kg),Height (cm),City
Alice,30,65,168,London
Bob,25,80,180,Manchester
Charlie,42,95,175,Birmingham
David,38,72,185,Leeds
Eve,29,58,160,Sheffield
Frank,45,88,190,Liverpool
Grace,33,60,172,Newcastle
Henry,27,75,178,Bristol
Ivy,40,90,165,Nottingham
Jack,36,68,182,Leicester

Hopefully it's useful.

[Fore Left]

Neat

I saved your test data as a text file, opened it in Excel, saved it as a xlsx and dropped it into the site and voila...

Name	Age	Weight (kg)	Height (cm)	City
Alice	30	65	168	London
Bob	25	80	180	Manchester
Charlie	42	95	175	Birmingham
David	38	72	185	Leeds
Eve	29	58	160	Sheffield
Frank	45	88	190	Liverpool
Grace	33	60	172	Newcastle
Henry	27	75	178	Bristol
Ivy	40	90	165	Nottingham
Jack	36	68	182	Leicester

I think Charlie needs to lose a few pounds though

[jagnet]

Yep, "Little Charlie" they call him

[jagnet]

I've added some more functionality to phables.co.uk

The tool is now split into two tabs: CSV to Forum Markup and Forum Markup to CSV

New Features

• Paste existing PH table markup and export it to CSV or Excel (Forum Markup to CSV)
• Live table editing (CSV to Forum Markup)

Editing an existing PH table

1. Paste the existing PH table markup into the 'Forum Markup to CSV' tab
2. Click the Switch View button
3. Use the live table editing features to make your edits
4. Click Copy to Clipboard to get the new, post-edit PH table markup to paste back into PH

Live table editing

• Modify table cell values
• Add & remove rows
• Move table rows up & down

NB, the preview table under Forum Markup to CSV isn't editable.


If anyone has any additional feature suggestions to make it more useful, let me know.

[David_M]

jagnet said:

I've added some more functionality to phables.co.uk

It is possible that I will never have a need for it, but kudos for making this!

[jagnet]

David_M said:

It is possible that I will never have a need for it, but kudos for making this!

thank you.

Tabular data matters

[Craikeybaby]

Great stuff!

I don't know how many people would have a use for it, but importing data from Google sheets might be good, especially if you want to play around with their auth/API...

[jagnet]

Ah, now that's a great idea!

[jagnet]

Google Sheets import now added under 'Other Import Options'.

It must be a publicly viewable sheet.

Read-only on a public sheet means that I can use the free API and there's no need to add complexity with API keys, authorisation, app permissions, etc.

Paste the URL as is and it'll figure out the Sheet ID and worksheet ID from that, or input these individually.

Edited by jagnet on Friday 28th February 17:40

[Craikeybaby]

Nice one!

[Ben Lowden]

This is brilliant thank you, we'll see if we can add this into the Formatting Help section