extract text from string

[LFKim2018]

how could I extract to 3 columns the following string format?
DATE SAMPLE OF STRING AMOUNT

first text is before the first occurrence of the space
2nd text is between the 1st space and the last space
3rd text is after the last space

say string is at A2
B2 = DATE
C2 = SAMPLE OF STRING (can contain more than 3 words)
D2 = AMOUNT

many many thanks

 

[LFKim2018]

I got the first and the last part ---
the only problem now is to extract the MID -
between the first space and the last space
many thanks

 

[LFKim2018]

the 1st text:
=LEFT(A2, SEARCH(" ",A2,1)-1)

the 3rd text:
=RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))),1))
whew... is there a shorter formula than this?

 

[sandy666]

if you are able to use PowerQuery (Get&Transform) try this:

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Column1.1", "Column1.2"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Column1.2", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Column1.2.1", "Column1.2.2"})
in
    #"Split Column by Delimiter1"[/SIZE]

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column1[/td][td][/td][td=bgcolor:#70AD47]Column1.1[/td][td=bgcolor:#70AD47]Column1.2.1[/td][td=bgcolor:#70AD47]Column1.2.2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]DATE SAMPLE OF STRING AMOUNT[/td][td][/td][td=bgcolor:#E2EFDA]DATE[/td][td=bgcolor:#E2EFDA]SAMPLE OF STRING[/td][td=bgcolor:#E2EFDA]AMOUNT[/td][/tr]
[/table]

 

[LFKim2018]

thank you for the prompt reply.
but am not using PowerQuery.

 

[sandy666]

no problem
have a nice day

 

[LFKim2018]

thank you

 

[Rick Rothstein]

the 3rd text:
=RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))),1))
whew... is there a shorter formula than this?

Yes...

=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",99)),99))

 

[LFKim2018]

Mr. Rothstein
Thank you for the formula!
can you help with the MID?
many many thanks

 

[Rick Rothstein]

Mr. Rothstein
Thank you for the formula!
can you help with the MID?
many many thanks

Try this...

=MID(LEFT(A2,LEN(A2)-LEN(D2)-1),LEN(B2)+2,99)