Split a Sentence at a specific point using excel formula

[kirupab390]

I need an help to split a sentence using excel formula:

For example see the below sentance

692 RUTLAND LLC 1,569.41 34 E 01820 182-183

I need only "692 RUTLAND LLC" alone from the above sentance. How can I achieve this using excel formula. Can anybody help me?

[#2]

@kirupab390 

Go to the cell and select this specific text, press CTRL-C, press ESC, go to the destination cell and press CTRL-V.

[#3]

If you want to extract the first 3 words in A1, the formula in B1 is:
=LEFT(A1,
FIND("|",
SUBSTITUTE(A1," ","|",3))-1)

[#4]

Thanks@Twifoo  for your immediate response. If I have any number at the end, then how can I extract the string? Like I asked in the question. Your help is much appreciated.

[#5]

What do you mean by "any number at the end"?

[#6]

HI@Twifoo 

If for example, my strings are

692 RUTLAND LLC 1,569.41 34 E 01820 182-183

711 GAS INC DON 20,991.72 34 E 06980

I need to separate the 692 RUTLAND LLC & 711 GAS INC DON from the above strings.. 

Thanks in Advance..

[#7 · Accepted solution]

A formula cannot be used unless there is a specific pattern for each string of your data.

[#8]

@Twifoo  Thanks for the advise.. Thanks for  your prompt reply..

[#9]

You're welcome!