+ Reply to Thread
Results 1 to 12 of 12

Trim from rows .

  1. #1
    Forum Contributor
    Join Date
    12-12-2017
    Location
    New Zealand
    MS-Off Ver
    Ms Office 2016
    Posts
    430

    Red face Trim from rows .

    Hello all ,

    I am hoping to get some functions that can give me the results I have already resulted for the first horse named
    American Red .

    I have some trim functions but unable to adapt them to this sheet .
    Results will come from data in cols O and P .

    There are many reference points I think that can help . The data is always quite clean and doesn?t change much .

    Date2 is always just inside left beside mostly four letters , but sometimes only 2 or 3 letters .
    Tab# is always left of horse name .
    Horse name is always beside-right of tab# .
    Plc comes from col O , and is to the left of the word of .
    Dist. is always left of letter m .
    Cond is always to right of letter m after a gap .
    Marg is always to left of capital letter L .
    Time is mostly mentioned in brackets but not always , seem to be right of letter m also . It would be great to get the time in
    custom format of 00:34.99 as an example as then these can be filtered in ascending order . But if a standard
    34.99 works for filtering that would be great .
    The 800 and 400 numbers always appear left of this @ .

    Hope I have explained it well .
    Thanks .
    Attached Files Attached Files
    I am grateful for all answers to my questions .
    Also i give a reputation even if not answered .

  2. #2
    Valued Forum Contributor
    Join Date
    07-23-2013
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    988

    Re: Trim from rows .

    This was very fiddly, and in a couple of the strings the characters that look like spaces were not, so see the attached sheet. Formulae at the bottom which can be copied to all rows.. For the "Time" column (L), it seems to often be given as e.g. "0:49.51 (600m 33.04)" which I assumed meant total time and then time @600m. Sometimes the 600m time was not given (possibly only for the first race as it was only 650m). The formula below gives the total time, rather than the 600m time.

    F2
    Please Login or Register  to view this content.
    G2
    Please Login or Register  to view this content.
    H2
    Please Login or Register  to view this content.
    I2
    Please Login or Register  to view this content.
    J2
    Please Login or Register  to view this content.
    K2
    Please Login or Register  to view this content.
    L2
    Please Login or Register  to view this content.
    M2
    Please Login or Register  to view this content.
    N2
    Please Login or Register  to view this content.
    Attached Files Attached Files

  3. #3
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Trim from rows .

    Please try at

    E2
    =IFERROR(--MID(P2,FIND(" ",P2)+1,7),"")

    F2
    =IF(P2="",-LOOKUP(0,-LEFT(O2,{1,2,3})),"")

    G2
    =IF(F2="","",MID(LEFT(O2,FIND("(",O2&" (")-2),LEN(F2)+1,99))

    H2
    =IF(E2="","",LEFT(O2,FIND(CHAR(160),O2)-1))

    I2
    =IF(E2="","",--SUBSTITUTE(MID(P2,FIND(" ",P2,3)+9,5),"m",))

    J2
    =IF(E2="","",SUBSTITUTE(MID(LEFT(P2,FIND(" ",P2,FIND("m",P2)+3)-1),FIND(I2,P2)+LEN(I2)+2,9),IFERROR(LOOKUP(99,--RIGHT(LEFT(P2,FIND(" ",P2,FIND("m",P2)+3)-1),{1,2})),),))

    K2
    =IF(E2&F2="",LOOKUP(99,--RIGHT(LEFT(P2,FIND("L,",P2&",")-1),{3,4,5})),"")

    L2
    =IF(K2="","",--MID(P2,FIND(TEXT(K2,"0.00L"),SUBSTITUTE(P2,")",))-7,5))

    M2:N2
    =IF($K2="","",IFERROR(--MID($P2,FIND(M$1,$P2)-5,2),""))
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    12-12-2017
    Location
    New Zealand
    MS-Off Ver
    Ms Office 2016
    Posts
    430

    Re: Trim from rows .

    Thanks to both , very happy with results .

  5. #5
    Forum Contributor
    Join Date
    12-12-2017
    Location
    New Zealand
    MS-Off Ver
    Ms Office 2016
    Posts
    430

    Re: Trim from rows .

    Hello back again , just realized that in col P to the very most left of data when there is only 3 letters , example E FM or K GR , etc .

    Im not getting results for cols E H I or J .
    Cols K L M and N are ok and resulting .

    Most of the this data does have 4 letters to left , seems to stem from the date and the rest then wont work .
    So basically just need to acknowledge when only 3 letters and the gap to the left .
    Thanks .

  6. #6
    Valued Forum Contributor
    Join Date
    07-23-2013
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    988

    Re: Trim from rows .

    Please upload an example of what?s not working and I?ll try and take a look tomorrow.

  7. #7
    Forum Contributor
    Join Date
    12-12-2017
    Location
    New Zealand
    MS-Off Ver
    Ms Office 2016
    Posts
    430

    Re: Trim from rows .

    Ok , here we go , thanks .

    Now found a couple with two letters and three , this is getting away from my original question . Understand if not dooable .

    Thanks .
    Attached Files Attached Files
    Last edited by I need excel help; 08-07-2022 at 08:45 PM. Reason: left info out .

  8. #8
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Trim from rows .

    Please try at E2

    =IFERROR(--MID(P2,MIN(FIND({0,1,2,3},P2&1230)),7),"")
    Attached Files Attached Files

  9. #9
    Valued Forum Contributor
    Join Date
    07-23-2013
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    988

    Re: Trim from rows .

    Looking at my earlier post it seems to have copied things from Excel as "*". On my screen they looked the same as a space. Thanks toBo_ry's solution I can see they should be CHAR(160).

    I see I missed column E in my first solution. Bo_Ry's update looks like it works for that. For H I and J my original solution seems to still work.

  10. #10
    Forum Contributor
    Join Date
    12-12-2017
    Location
    New Zealand
    MS-Off Ver
    Ms Office 2016
    Posts
    430

    Re: Trim from rows .

    Thanks guys , I have cleaned up my sheet and only see a last problem when 2 letters with gap and 1 letter .
    Enclosed is another sample sheet highlighted in yellow .

    I very happy to get to this point and can live with a few hundred that may not be able to result . Overall all of the functions are sourcing the data . Thought I would come back one more time otherwise I will mark as solved .
    Attached Files Attached Files

  11. #11
    Valued Forum Contributor
    Join Date
    07-23-2013
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    988

    Re: Trim from rows .

    Try this in column I:

    =IF(AND(O2<>"",P2<>""),MID(P2,FIND(CHAR(160),P2)+1,FIND("m",P2,FIND(CHAR(160),P2)+1)-(FIND(CHAR(160),P2)+1)),"")

  12. #12
    Forum Contributor
    Join Date
    12-12-2017
    Location
    New Zealand
    MS-Off Ver
    Ms Office 2016
    Posts
    430

    Re: Trim from rows .

    Great , that fixed it .
    Thanks again guys problem absolutely solved .

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Trim data to all one row from adjacent rows .
    By I need excel help in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-14-2022, 04:51 AM
  2. [SOLVED] trim to match two sets of input-trim spaces around a character
    By nigelog in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-21-2017, 12:33 PM
  3. VLOOKUP + TRIM, TRIM Lookup table array...
    By mangesh.mehendale in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-19-2016, 02:10 AM
  4. How to trim the unused rows and collumns
    By docodao in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-03-2016, 11:10 PM
  5. Replies: 0
    Last Post: 08-13-2015, 07:57 AM
  6. Replies: 4
    Last Post: 05-25-2009, 12:25 PM
  7. Trying to trim a trailing space char and the Trim function isn't working
    By Psychochook in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-30-2007, 11:28 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1