Forum Discussion

Copper Contributor

Mar 16, 2019

Solved

Split a Sentence at a specific point using excel formula

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 sent...

excel

Formulas and Functions

Twifoo
Mar 16, 2019
A formula cannot be used unless there is a specific pattern for each string of your data.

Twifoo

Silver Contributor

Mar 16, 2019

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

kirupab390

Copper Contributor

Mar 16, 2019

ThanksTwifoo 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.

Twifoo
Silver Contributor
Mar 16, 2019
What do you mean by "any number at the end"?
- kirupab390
  Copper Contributor
  Mar 16, 2019
  HITwifoo
  
  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..
  - Twifoo
    Silver Contributor
    Mar 16, 2019
    A formula cannot be used unless there is a specific pattern for each string of your data.