Set number as TEXT for copy/paste

[athegn]

Having missed the first deadline, warning to youngsters elderly health problems are not solved by on epill and feeling fit in an hour or two, I am returning to this problem.

I have two formulae that must return TEXT:-

First forumila gives correct answer e.g.

=LEFT(RIGHT(A4,5),2)&":"&RIGHT(A4,2)

where A4 is “Wed 10.30”

answer “10:30” i.e as TEXT

Formula

=CEILING(LEFT(K4,SEARCH(" “,K4)-1)/VLOOKUP(J4,Difficulty,2,0)/24+Break+IF(VALUE(LEFT(K4,SEARCH(” ",K4)-1))<6,0,Lunch)+Flexibility+I4,“00:30”)

Where K4 is TEXT and Difficulty, Brfeak,Lunch and Flexibity are numbers. I4 is the result of the first formula i.e. TEXT.

I need to convert Y4, where this forumla is, to TEXT. But I can’t seem to get the right forumula =TEXT(Y4,“00”:“00”) gives ERR502 what is missing?

I copy/paste the results, from columns A to AL, into another spreadsheet that requires all input to be in text format; that is why it accepts I4 because it is “10:30” whereas Y4 is a number.

Any advice please?

[schiavinatto]

Post an example file, it's easier.

[athegn]

How do I attached a file?

[athegn]

I think I have find how to upload an example file?

Example file.ods (38.4 KB)

Cols I and Y on sheet PDF relate and Cols H an dR on Sheet Convert to csv

This is heavy edited to remove personal data.

Sheet PDF comes from the PDF I am supplied with,: Adobe converts to xls format. Walks Manager bulk upload is the format I have to use to upload.

[schiavinatto]

See in the file the formula in Y4…
Example file_GS.ods (38.5 KB)

[athegn]

Thank you.

It is going to take me sometime to fully grasp that formula|

[joshua4]

It really does need to be said that it would be better to keep your raw data as raw data that matches your processing needs, then use your time and effort to print reports from that data. Why record the day of the week in A:A when you can just record the date as a date/time value then have Calc give you back the day of the week as needed when needed? Why denormalize A:A at all with month headers? Again, use a date/time value then have Calc give you the month as/when needed. Etc.

I’ve attached a partial example of what a more normalized data collection sheet might look like.

NormalizingWalkData.ods (12.3 KB)

As for the function/formula, the fx button left of the edit line will be of some help. But personally, I’d want to see it in a textual pretty print:

NormalizeWalkData.odt (15.8 KB)

[athegn]

Example file_GS_bigtext.ods (39.4 KB)

schiavinatto

Thank you for your work but I have hit a snag. I obviously have not grasped the full nuances of the formula.

When I copied your formula to the real spread sheet and dragged down I find that some fields had the resulting time as say 13:00 or 16:30 but a lot of the others had 1300 or 1630 i.e missing the colon.

When I tried to understand your formula I realised that I was adding a complication by storing the Miles as say “8 miles appox” whereas all I needed to do was store the number 8.

I attach the amended ods file.

I would be very grateful if you could look at this file and tell why I am getting times with the missing colon?

Regards

athegn

[athegn]

joshua4

I think you are saying that the PDF should be amended. Unfortunately I cannot control what is put in the PDF file, I am given.

regards
athegn

[schiavinatto]

Test

Example file_GS_bigtext_GS.ods (41.9 KB)

[athegn]

Example file_GS_bigtext.ods (40.0 KB)

schiavinatto

Thank you but I am really sorry but there still is a problem when I copy/paste to real spreadsheet.

I have managed to find what will trigger the problem but not why!

It seems that changing to difficulty can affect when I get 15:30 or 1530

I attach to test spreadsheet

If you change the “M” in Cell D4 to “L” the colon disappears

If you change the “L” in cell D7 to “M” the colon appears. Similarly if you change the “M” in cell D8 to “L” the colon appears.

I can’t see why.

Would you mind having another look please?

Regards

athegn