Separating text into seperate fields

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

vanced - 19 May 2008 15:46 GMT

I have several long strings of text in which I would like to separate the
first sentance ending in ". " into another field. Which function would I need
to use. Any help would be greatly appreciated.

Example:
(1) Conformance. Cylinders shall comply with DOT specifications and shall
be.....

(1) Conformance. (cell 1)
Cylinders shall comply with DOT specifications and shall be.....(cell 2)

Reply to this Message

Mike H - 19 May 2008 16:00 GMT

Hi,

To extract the first bit try
=LEFT(A1,FIND(".",A1,1)-1)
and the second bit
=MID(A1,FIND(".",A1,1)+2,LEN(A1))

For the second bit you may have to adjust the +2 to allow for whether the
space is really there after the full stop.

Mike

> I have several long strings of text in which I would like to separate the
> first sentance ending in ". " into another field. Which function would I need
[quoted text clipped - 6 lines]
> (1) Conformance. (cell 1)
> Cylinders shall comply with DOT specifications and shall be.....(cell 2)

Reply to this Message

Mike H - 19 May 2008 16:02 GMT

Changed my mind about the second one you can ignore the comment on spaces,
use this

=TRIM(MID(A1,FIND(".",A1,1)+1,LEN(A1)))

Mike

Reply to this Message

David Biddulph - 19 May 2008 16:06 GMT

=LEFT(A1,FIND(".",A1))
=RIGHT(A1,LEN(A1)-FIND(".",A1))
--
David Biddulph

>I have several long strings of text in which I would like to separate the
> first sentance ending in ". " into another field. Which function would I
[quoted text clipped - 7 lines]
> (1) Conformance. (cell 1)
> Cylinders shall comply with DOT specifications and shall be.....(cell 2)

Reply to this Message