Find text in between to knowns
 

I am currently taking a long string and converting it from text to
columns. I am trying to extract the text in between the "Title:" and
"Start" As you can see below, when I convert to columns, I have
different columns and the number of cells the title occupies varies as
well.

A B C D
E F G
Template Title: National Folk Festival Start
Title: Washington, DC (one day
tour) Start
Title: The Parks Start
Title: The Parks Start

In the end I need it to look like
A B C D E
National Folk Festival
Washington, DC (one day tour)
The Parks
The Parks

I have a lot of rows of this type of data, so I need VB to do it
automatically for me. Any help you can give is appreciated.

Find text in between to knowns
 

On Jul 9, 4:29 pm, wrote:
I am currently taking a long string and converting it from text to
columns. I am trying to extract the text in between the "Title:" and
"Start" As you can see below, when I convert to columns, I have
different columns and the number of cells the title occupies varies as
well.

A B C D
E F G
Template Title: National Folk Festival Start
Title: Washington, DC (one day
tour) Start
Title: The Parks Start
Title: The Parks Start

In the end I need it to look like
A B C D E
National Folk Festival
Washington, DC (one day tour)
The Parks
The Parks

I have a lot of rows of this type of data, so I need VB to do it
automatically for me. Any help you can give is appreciated.

You can use a function, copy and paste the values of the function, and
then do a text to columns.

=MID(A2,FIND(":",A2)+1,LEN(A2)-FIND(":",A2)-5)

FIND locates the position number of the string that you want to find
LEN counts the number of characters in the string
and the -5 is for the number of characters in the word "Start"

Thus, MID will start at the ":" position and take the number of
characters from the ":" to the end -5 for the word "Start"

This should work.

Matt

Find text in between to knowns
 

On Jul 9, 5:16 pm, wrote:
On Jul 9, 4:29 pm, wrote:





I am currently taking a long string and converting it from text to
columns. I am trying to extract the text in between the "Title:" and
"Start" As you can see below, when I convert to columns, I have
different columns and the number of cells the title occupies varies as
well.

A B C D
E F G
Template Title: National Folk Festival Start
Title: Washington, DC (one day
tour) Start
Title: The Parks Start
Title: The Parks Start

In the end I need it to look like
A B C D E
National Folk Festival
Washington, DC (one day tour)
The Parks
The Parks

I have a lot of rows of this type of data, so I need VB to do it
automatically for me. Any help you can give is appreciated.

You can use a function, copy and paste the values of the function, and
then do a text to columns.

=MID(A2,FIND(":",A2)+1,LEN(A2)-FIND(":",A2)-5)

FIND locates the position number of the string that you want to find
LEN counts the number of characters in the string
and the -5 is for the number of characters in the word "Start"

Thus, MID will start at the ":" position and take the number of
characters from the ":" to the end -5 for the word "Start"

This should work.

Matt- Hide quoted text -

- Show quoted text -

Sorry, I forgot to mention that you need to add the TRIM function into
the set. If you have spaces AFTER the word "Start" then the -5 won't
return what you are looking for. TRIM takes off the spaces infront
and behind the text.

Matt