How to split cells to create a new cell starting from a constant character per row

New Contributor

The worksheet has a single column A with each row containing text in which is included "1." I want to cut and paste everything after the "1." ( including the "1.") and put it in column B as an automated process for the 1000+ rows in the worksheet. 

example:

Row A1 'This is the text 1. this is the list of tems 2. more text, 3. more text'

 

Desired result

  A1                       B1

'This is the text'   '1. this is the list of tems 2. more text, 3. more text'

 

2 Replies

@britso Please see attached file. It contains three possible scenarios, and likely that there are many more.

 

1. Power Query

2. using TEXTBEFORE and TEXTAFTER (new Insider/Beta functions)

3. using LEFT, RIGHT, FIND and LEN (with dynamic arrays)

4. same as 3 but without dynamic arrays.

 

See is either of these works for you.

 

Yes the TextAfter/before solved it. Thank you very much for the quick response