> On 5 Jul 2022, at 21:39, Brian Barker <b.m.bar...@btinternet.com.INVALID> > wrote: > > At 17:26 04/07/2022 +0100, David Deeks wrote: >> ATTN Brian Barker? > > I trust others are permitted to offer assistance?
Sorry Brian, I was just being courteous - I thought if I included everyone with my follow-up query it would look like I was doubting your expertise! > >> Here is where I have hit the problem. I am attaching a copy of the full >> spreadsheet as it usually is, sorted on columns A,I,B ... >> > and the same spreadsheet but with column I >> converted using "Text to columns". If you go to the bottom you will see >> that there is one more row at the end of column I, and the values in the >> rows above are one row below where they should be. > > I dealt with this issue in my last message. The "Text to Columns" trick will > work for both your four-digit numbers and text, but goes awry for your rogue > value which was in I1812. There you had "*2011 **1996" with a line break > separating the two parts. "Text to Columns" divides this into two rows of the > column - not what you need. As I said, you will have to deal with this first > - perhaps by deleting the value and reinserting it after the conversion. But > in any case, how do you wish or expect this value to sort amongst other > values in Column I anyway? Note that this rogue value has moved to I1241 in > your latest version of the spreadsheet. My fault entirely with this one Brian. Firstly I attached the wrong version of my ‘text to columns’ spreadsheet. I had in fact made the corrections you had highlighted on the version I should have attached, but still with the problem I described. My second mistake however was not to realise that as well as cells containing two years, cells containing one year followed by a single asterisk were also evidently unacceptable and led to strange effects - because having found several of these and removed the asterisks, everything now seems to be hunky dory. So ‘text to columns’ converts to text those cells originally entered as numbers? Thanks so much again and apols for the above. I promise not to single you out for any more help I may need in future - but I really have appreciated it. All best David > > I trust this helps. > > Brian Barker > > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org > For additional commands, e-mail: users-h...@openoffice.apache.org >
