[Solved] Cells are empty after Paste Special Transpose

[CrankyEngineer]

I'm importing electricity price data. They come as text in a two column table. The first column is the time interval to which a price applies, and the second, the price in the form 12.34p/kWh. I need the price information in a row that I can copy and paste into a table of the prices for each day. Initially I manually deleted the letters to just leave the digits in the column, copied the resulting column of prices, and pasted special with transpose ticked into a vacant row. So far so good. To automate part of the process I then tried text functions to remove the p/kWh part and put the results into the next column. However although the result looks the same as doing it manually, when I copy and transpose the results into a row, all I get are blank cells. What is going wrong? I assume it's something to do with the data being text format not number. If so, why does the manual delete method work and text functions don't? If it is a text / number issue, is it possible to convert the text number into number format? I've searched Help and tried everything that I can think of, starting with making the destination column number format but so far nothing has worked, so I'm back to manual delete unless anyone out there has a solution.

[Alex1]

This isn't enough information. We need to know how the table looks like and how you proceed exactly, so it may help to upload an example of the input data. No screenshots please.

[FJCC]

@CrankyEngineer - also what options have you selected in the Paste Special dialog?

[CrankyEngineer]

Thank you Alex1 and FJCC. Here is a copy of the contents of my table, and some further detail below which hopefully address both your questions.

A              B            C                  D
Time Slot      Price        No of Characters   Stripped Price (p)
00:00 - 00:30  16.98p/kWh   10                 16.98
00:30 - 01:00  18.08p/kWh   10                 18.08
01:00 - 01:30  17.61p/kWh   10                 17.61
01:30 - 02:00  17.64p/kWh   10                 17.64
02:00 - 02:30  16.56p/kWh   10                 16.56
02:30 - 03:00  17.11p/kWh   10                 17.11
03:00 - 03:30  16.05p/kWh   10                 16.05
03:30 - 04:00  15.71p/kWh   10                 15.71
04:00 - 04:30  17.2p/kWh     9                 17.2
04:30 - 05:00  15.66p/kWh   10                 15.66
05:00 - 05:30  17.2p/kWh     9                 17.2
05:30 - 06:00  17.09p/kWh   10                 17.09
06:00 - 06:30  18.27p/kWh   10                 18.27
06:30 - 07:00  18.52p/kWh   10                 18.52]

Copy Agile price from Octopus Incoming page.
Paste result in columns A & B starting at Cell A1.

Formulae are: Column C =LEN(B2) Column D =LEFT(B2;(C2-5))

I then copy the entries in column D and paste them into the day by day spread sheet as a row where all the columns are formatted as Number 1234.00 using "Paste Special - Transpose" with only Number selected. The result is empty cells in the row. If I also tick Text, I get the "numbers" all taged as Text, and have to manually delete the ' from each "number".
So to reframe my question, is the problem down to using Transpose, or is it that stripping the letters out of the Price data using text functions instead of deleting them manually makes the text "numbers" uncovertable to number format?

[MrProgrammer]

… using "Paste Special - Transpose" with only Number selected. The result is empty cells in the row.

Your formula created text. There were no numbers to paste, hence the cells stay empty.

=LEFT(B2;(C2-5))
Is the problem down to using Transpose, or is it that stripping the letters out of the Price data using text functions instead of deleting them manually makes the text "numbers" uncovertable to number format?

The latter. LEFT always returns text. If you want numbers use VALUE, either =VALUE(LEFT(B2;(C2-5))) or =VALUE(SUBSTITUTE(B2;"p/kWh";""))).

It would have been easier for everyone if you had attached your spreadsheet instead of putting the data in your post and then explaining what the formulas were. Please attach your spreadsheet in future posts.

If this solved your problem please go to your first post use the Edit ✏️ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

[Alex1]

It's much easier to delete p/kWh using Edit > Find & Replace. Then the result will be currency if you had formatted the column as such in advance.