Splitting columns in Excel 2003 and XP

  exdragon 12:51 23 Nov 09
Locked

Hi - I've got a spreadsheet which has columns of titles of photographs together with the number of points they have been awarded in a competition. The titles can vary from one word upwards, and they're shown as, for example, 'Early morning 9 points'

The spreadsheet from which this information is taken cannot be altered at all, as it is part of an integrated package used specifically for these types of competitions.

I'd like to be able to show the '9 points' in a separate column after the title (in my copy of the spreadsheet). Using Text to Columns won't work, as I don't know how many 'extra' columns to add to take into account the number of words in the titles.

Is there any way to move just the last two items (the '9' and the 'points') into a separate column?

  interzone55 13:16 23 Nov 09

Hopefully Vog will see this, but in the meantime I'll try

are the points single digit, so the text to be moved is always 8 digits...

  exdragon 13:29 23 Nov 09

No - can be from 6 to 15.

What I've also just realised is that, if an image is accepted for the competition, there can be either (A) or (HM) after the word points. Both letters are in brackets and need to stay with the number of points shown.

  VoG II 13:40 23 Nov 09

This will do it I think

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),198))

  exdragon 13:48 23 Nov 09

Thanks, VoG™, but what do I do with it??

  VoG II 13:51 23 Nov 09

Say your first record is in A1 then in a spare column in row 1 enter the formula and drag down as far as necessary.

If the data actually starts in M2 then in a spare column row 2 enter

=TRIM(RIGHT(SUBSTITUTE(M2," ",REPT(" ",99)),198))

and drag down.

  exdragon 14:01 23 Nov 09

VoG™ - I've said it before and I'll say it again. will you marry me?

Thanks!

  exdragon 14:02 23 Nov 09

But not yet - it's picked up the 'points (A)' but not the actual number of points.

Unless I've missed something

  VoG II 14:06 23 Nov 09

If it is basically the last three 'words' then try

=MID(TRIM(A1),FIND(CHAR(1),SUBSTITUTE(TRIM(A1)," ",CHAR(1),LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))-2))+1,255)

  exdragon 14:12 23 Nov 09

Hmm, perhaps it isn't possible - here's a sample

1. After The Fire 9 Points
1. Dancing 9 Points
1. Misty Morning 9 Points
1. Nearly there Girl 12 Points (A)

So as you can see, it can be 2 words or 3 if there's an (A) included.

Might be bit too complicated, do you think?

  VoG II 14:17 23 Nov 09

Starts to get awkward. Try

=IF(ISNUMBER(FIND("(",A1)),MID(TRIM(A1),FIND(CHAR(1),SUBSTITUTE(TRIM(A1)," ",CHAR(1),LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))-2))+1,255),TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),198)))

This thread is now locked and can not be replied to.

Surface Pro 5 News - release date, UK price, features, specs

Microsoft Surface Studio hands-on review: the iMac killer is here

Best Mac antivirus 2017