deleting and changing order of words

  • Hi


    I need to change order of words in an excel column


    Like for instance i would like delete the word DISCHARGE LINE and SUCTION LINE from where it is and place it after year. I have around 9000 entries already made and need to change the order as previously requested. Can you help me with that.


    [TABLE="width: 567"]

    [tr]


    [td]

    2004 AUDI A4 QUATTRO DISCHARGE LINE FROM VIN 3320001 BASE MODEL-V6 3.0L

    [/td]


    [/tr]


    [tr]


    [td]

    2004 AUDI A4 QUATTRO DISCHARGE LINE FROM VIN 3320001 CABRIOLET-V6 3.0L

    [/td]


    [/tr]


    [tr]


    [td]

    2003 AUDI A4 QUATTRO DISCHARGE LINE FROM VIN 3320001 AVANT-V6 3.0L GASFI

    [/td]


    [/tr]


    [tr]


    [td]

    2003 AUDI A4 QUATTRO DISCHARGE LINE FROM VIN 3320001 BASE MODEL-V6 3.0L

    [/td]


    [/tr]


    [tr]


    [td]

    2006 AUDI TT SUCTION LINE BASE MODEL-L4 1.8L GASFI T

    [/td]


    [/tr]


    [tr]


    [td]

    2005 AUDI TT SUCTION LINE BASE MODEL-L4 1.8L GASFI T

    [/td]


    [/tr]


    [tr]


    [td]

    2004 AUDI TT SUCTION LINE BASE MODEL-L4 1.8L GASFI T

    [/td]


    [/tr]


    [tr]


    [td]

    2003 AUDI TT SUCTION LINE BASE MODEL-L4 1.8L GASFI T

    [/td]


    [/tr]


    [tr]


    [td]

    2002 AUDI TT SUCTION LINEBASE MODEL-L4 1.8L GASFI T


    Thanks

    [/td]


    [/tr]


    [/TABLE]

  • Re: deleting and changing order of words


    In an adjacent column, try:


    =IF(ISNUMBER(FIND("DISCHARGE LINE",A1)),SUBSTITUTE(SUBSTITUTE(A1,"DISCHARGE LINE","")," "," DISCHARGE LINE ",1),IF(FIND("SUCTION LINE",A1),SUBSTITUTE(SUBSTITUTE(A1,"SUCTION LINE","")," "," SUCTION LINE ",1),A1))


    where first original string is in A1.


    copied down.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: deleting and changing order of words


    Lol... that is so much easier than what I came up with... which would definitely help over 9000 lines


    =IF(IF(ISERROR(SEARCH("DISCHARGE LINE",C5,1)),FALSE,TRUE)=TRUE,CONCATENATE((LEFT(C5,((SEARCH("DISCHARGE LINE",C5,1))-2))),((RIGHT(C5,(SEARCH("DISCHARGE LINE",C5,1))+(LEN("DISCHARGE LINE")))))),IF((IF(ISERROR(SEARCH("SUCTION LINE",C5,1)),FALSE,TRUE))=TRUE,CONCATENATE((LEFT(C5,((SEARCH("SUCTION LINE",C5,1))-2))),((RIGHT(C5,(SEARCH("SUCTION LINE",C5,1))+(LEN("SUCTION LINE")))))),c5))

  • Re: deleting and changing order of words


    If there were many more similar substitutions, you could make a list of the strings to move separately, say in N1:N5


    then use the formula:


    =SUBSTITUTE(SUBSTITUTE(A1,LOOKUP(10^10,FIND($N$1:$N$5,A1),$N$1:$N$5),"")," "," "&LOOKUP(10^10,FIND($N$1:$N$5,A1),$N$1:$N$5)&" ",1)

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: deleting and changing order of words


    Thank you so much. It really helps and its so easy. Now i see other replacements in the same column of 9000 entries. Can you also do the same function for delete/add the word LIQUID LINE, "SUCTION AND DISCHARGE ASSEMBLY" in the SAME code you write. IT WOULD BE OF GREAT HELP



    [TABLE="width: 567"]

    [tr]


    [td]

    1999 LIQUID LINE ACURA CL BASE MODEL-L4 2.3L GASFI N

    [/td]


    [/tr]


    [tr]


    [td]

    1999 LIQUID LINE ACURA CL BASE MODEL-V6 3.0L GASFI N

    [/td]


    [/tr]


    [tr]


    [td]

    1998 LIQUID LINE ACURA CL BASE MODEL-L4 2.3L GASFI N

    [/td]


    [/tr]


    [tr]


    [td]

    1998 LIQUID LINE ACURA CL BASE MODEL-V6 3.0L GASFI N

    [/td]


    [/tr]


    [tr]


    [td]

    1989 BUICK SKYHAWK SUCTION AND DISCHARGE ASSEMBLY BASE MODEL-L4 2.0L GASFI N

    [/td]


    [/tr]


    [tr]


    [td]

    1999 BUICK CENTURY SUCTION AND DISCHARGE ASSEMBLY CUSTOM-V6 3.1L GASFI N

    [/td]


    [/tr]


    [tr]


    [td]

    1999 BUICK CENTURY SUCTION AND DISCHARGE ASSEMBLY LIMITED-V6 3.1L GASFI N

    [/td]


    [/tr]


    [tr]


    [td]

    1998 BUICK CENTURY SUCTION AND DISCHARGE ASSEMBLY CUSTOM-V6 3.1L GASFI N

    [/td]


    [/tr]


    [tr]


    [td]

    1998 BUICK CENTURY SUCTION AND DISCHARGE ASSEMBLY LIMITED-V6 3.1L GASFI N

    [/td]


    [/tr]


    [tr]


    [td]

    1997 BUICK CENTURY SUCTION AND DISCHARGE ASSEMBLY CUSTOM-V6 3.1L GASFI N

    [/td]


    [/tr]


    [tr]


    [td]

    1997 BUICK CENTURY SUCTION AND DISCHARGE ASSEMBLY LIMITED-V6 3.1L GASFI N

    [/td]


    [/tr]


    [/TABLE]

  • Re: deleting and changing order of words


    If you go with my last example and add your replacement phrases in N1:N4 and apply formula (see attached):


    =SUBSTITUTE(SUBSTITUTE(A1,LOOKUP(10^10,FIND($N$1:$N$4,A1),$N$1:$N$4),"")," "," "&LOOKUP(10^10,FIND($N$1:$N$4,A1),$N$1:$N$4)&" ",1)


    Then it should work.


    If you add more items in column N, change the ranges in formula to match. Or you can create a Dynamic Named Range in column N, and use that name in the formula instead of range.

  • Re: deleting and changing order of words


    Now its a bit difficult to do it this way as my knowledge in excel is not so advanced
    Would appreciate if you could do it similar way as you did previously by adding/deleting the words DISCHARGE LINE, SUCTION LINE, LIQUID LINE, and SUCTION AND DISCHARGE ASSEMBLY


    Thanks

  • Re: deleting and changing order of words


    Do you mean you just want the single formula for all 4 items?

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: deleting and changing order of words


    If so, then:

    =IF(ISNUMBER(FIND("DISCHARGE LINE",A1)),SUBSTITUTE(SUBSTITUTE(A1,"DISCHARGE LINE","")," "," DISCHARGE LINE ",1),IF(ISNUMBER(FIND("SUCTION LINE",A1)),SUBSTITUTE(SUBSTITUTE(A1,"SUCTION LINE","")," "," SUCTION LINE ",1),IF(ISNUMBER(FIND("LIQUID LINE",A1)),SUBSTITUTE(SUBSTITUTE(A1,"LIQUID LINE","")," "," LIQUID LINE ",1),IF(ISNUMBER(FIND("SUCTION AND DISCHARGE ASSEMBLY",A1)),SUBSTITUTE(SUBSTITUTE(A1,"SUCTION AND DISCHARGE ASSEMBLY","")," "," SUCTION AND DISCHARGE ASSEMBLY ",1),A1))))

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: deleting and changing order of words


    Thank you so much for the quick reply. It did not work with the substitute formula, but it did worked with the first one that you provided. Its just that i could not manipulate the substitute formula. But your formula really saved me a lot of time. Since i'm new to this forum, is it something that i should do now, that i got my response and is satisfied


    Thanks

  • Re: deleting and changing order of words


    Thank you for the reply. I went for the code that id NBVC provided. Let me know if i should do something given that you helped me out


    Thanks

  • Re: deleting and changing order of words


    Thanks for the feedback. No further action is required by you.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!