• Formula to pull first word from text string in a column

    From igelcommunity@gmail.com@21:1/5 to David Biddulph on Thu Jan 16 19:50:16 2020
    This is great! I've been looking for this... Now, is there a way to extract the last word in a string ONLY if it is not the only word in the string. If it is then i want it to be found in the formula you wrote above. I'm basically trying to extract
    three names but not always is there a second or even a third name, for example, Cher. I have it all working but the last name will duplicate the first if it is a one word name. :(


    On Thursday, January 8, 2009 at 2:00:56 PM UTC-5, David Biddulph wrote:
    =LEFT(A2,FIND(" ",A2)-1)

    You may want to add some error handling if there isn't always more than one word.
    =IF(ISNUMBER(FIND(" ",A2)-1),LEFT(A2,FIND(" ",A2)-1),A2)
    --
    David Biddulph

    "CrisT" <CrisT@discussions.microsoft.com> wrote in message news:DF295315-D059-4376-9A78-3662D35739DF@microsoft.com...
    Hello, I was hoping someone could help me with a formula. I have a column in
    a spreadsheet that has the following types of text (for example):

    Subaru WRX
    Subaru STI
    Jeep Wrangler
    Jeep Grand Cherokee

    What I am looking for is a formula to pull the first word from the column and put it in another column, say column T.

    Thank you in advance!


    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Roger Govier@21:1/5 to igelco...@gmail.com on Fri Jan 17 04:25:42 2020
    On Friday, 17 January 2020 03:50:19 UTC, igelco...@gmail.com wrote:
    This is great! I've been looking for this... Now, is there a way to extract the last word in a string ONLY if it is not the only word in the string. If it is then i want it to be found in the formula you wrote above. I'm basically trying to extract
    three names but not always is there a second or even a third name, for example, Cher. I have it all working but the last name will duplicate the first if it is a one word name. :(

    Hi
    I would use a helper column.
    With your data in column A, starting in A2, enter in B2 =LEN(A2)-LEN(SUBSTITUTE(A2," ",""))
    This will tell you how many spaces you have in column A
    Then, in C2 enter
    =LEFT(A2,FIND(" ",A2&" ")-1) and copy down to give you all first names
    In D2 enter
    =IF(B2=2,MID(A2,FIND(CHAR(160),SUBSTITUTE(A2," ",CHAR(160),1))+1,FIND(CHAR(160),SUBSTITUTE(A2," ",CHAR(160),1))-1),"")
    and copy down which will extract the middle name, if there is one.
    Finally in E2 enter
    =IF(B2>0,MID(A2,FIND(CHAR(160),SUBSTITUTE(A2," ",CHAR(160),B2))+1,256),"")
    and copy down, which will find the last name, if there is one.

    Hope this helps.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From jackie.puleio@gmail.com@21:1/5 to All on Fri Jan 24 15:08:23 2020
    Hello,

    The =split( cell, "character") function would do this. You'll just have to make sure that you have enough columns to the right to accommodate this split.

    For your example, you would need 3 blank columns to the right available.

    Assuming cells are listed from A1:A4, to get the type of car, you'll need to split using a space character (" ") using the formula below.

    =split(A1, " ")
    This will result in
    A1 = Suburu WRX in B1 = split(A1,"") and showing in B1 = Suburu C1=WRX

    ...

    =split(A4, " ")
    This will result in
    A4 = Jeep Grand Cherokee in B1 = split(A4,"") and showing in B4 = Jeep C4=Grand D4 = Cherokee

    I hope this helps!

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Guo Chen@21:1/5 to jackie...@gmail.com on Tue Nov 15 01:44:16 2022
    On Saturday, January 25, 2020 at 7:08:27 AM UTC+8, jackie...@gmail.com wrote:
    Hello,

    The =split( cell, "character") function would do this. You'll just have to make sure that you have enough columns to the right to accommodate this split.

    For your example, you would need 3 blank columns to the right available.

    Assuming cells are listed from A1:A4, to get the type of car, you'll need to split using a space character (" ") using the formula below.

    =split(A1, " ")
    This will result in
    A1 = Suburu WRX in B1 = split(A1,"") and showing in B1 = Suburu C1=WRX

    ...

    =split(A4, " ")
    This will result in
    A4 = Jeep Grand Cherokee in B1 = split(A4,"") and showing in B4 = Jeep C4=Grand D4 = Cherokee

    I hope this helps!


    My request is pretty much the same but with one tweak:

    if it has multiple words, I want the first one. If it is only a one-word string then I just want that one. =LEFT(A2,FIND(" ",A2)-1) does not work on the latter part. Any idea?

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Philip Herlihy@21:1/5 to All on Tue Nov 15 11:22:18 2022
    In article <e064849a-6ab6-4c6f-9b3f-96e45f33035dn@googlegroups.com>, Guo Chen wrote...

    On Saturday, January 25, 2020 at 7:08:27 AM UTC+8, jackie...@gmail.com wrote:
    Hello,

    The =split( cell, "character") function would do this. You'll just have to make sure that you have enough columns to the right to accommodate this split.

    For your example, you would need 3 blank columns to the right available.

    Assuming cells are listed from A1:A4, to get the type of car, you'll need to split using a space character (" ") using the formula below.

    =split(A1, " ")
    This will result in
    A1 = Suburu WRX in B1 = split(A1,"") and showing in B1 = Suburu C1=WRX

    ...

    =split(A4, " ")
    This will result in
    A4 = Jeep Grand Cherokee in B1 = split(A4,"") and showing in B4 = Jeep C4=Grand D4 = Cherokee

    I hope this helps!


    My request is pretty much the same but with one tweak:

    if it has multiple words, I want the first one. If it is only a one-word string then I just want that one. =LEFT(A2,FIND(" ",A2)-1) does not work on the latter part. Any idea?

    If you don't need a formula (which would mean cells are updated dynamically if the 'source' changes) then Flash Fill can be useful, and it does work in your situation where only one word is present.

    https://www.youtube.com/watch?v=1KimYFzET1w

    --

    Phil, London

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)