Hello,

I have a spreadsheet (excel 2007) with many ( a few hundred) lines of text.
Each line is in a single cell (A1, A2, A3, ... ) and is quite long. I want
to split the line into several chunks, with each chunk in a different cell
on the same line. The chunk 'boundaries' can be identified by keywords or
characters, eg *, 'name'. I think this is called parsing ?

Is there a way of doing this easily & quickly for all the lines without
writing a macro?. I have no experience of macros & don't want to spend a lot
of time learning for what will probably be a one-off project.

Hope this makes sense.

Thanks for advice, hints & tips etc

KK

On Sat, 8 Mar 2008 12:31:35 -0000, "KRK"
wrote:

Hello,

I have a spreadsheet (excel 2007) with many ( a few hundred) lines of text.
Each line is in a single cell (A1, A2, A3, ... ) and is quite long. I want
to split the line into several chunks, with each chunk in a different cell
on the same line. The chunk 'boundaries' can be identified by keywords or
characters, eg *, 'name'. I think this is called parsing ?

Is there a way of doing this easily & quickly for all the lines without
writing a macro?. I have no experience of macros & don't want to spend a lot
of time learning for what will probably be a one-off project.

Hope this makes sense.

Thanks for advice, hints & tips etc

KK

It can be done easily once you define the "chunk boundaries".

Macros are fairly simple to implement, but, depending on the nature of your
boundaries, formulas may also be fairly simple.

--ron

Hi,

You dont provide too much information but in principal parsing text is
fairly straightforward. Using the sentence:-

my name is MikeH and this is a sentence
=LEFT(A1,FIND("MikeH",A1,1)+5)
extracts 'my name is MikeH'

This formula
=MID(A1,FIND("MikeH",A1,1),999)
extracts 'MikeH and this is a sentence'

and if you want a bit out of the middle
=MID(A1,FIND("MikeH",A1,1),5)
extracts 'MikeH'

Hope this gets you off in the right direction.

Mike

"KRK" wrote:

Hello,

I have a spreadsheet (excel 2007) with many ( a few hundred) lines of text.
Each line is in a single cell (A1, A2, A3, ... ) and is quite long. I want
to split the line into several chunks, with each chunk in a different cell
on the same line. The chunk 'boundaries' can be identified by keywords or
characters, eg *, 'name'. I think this is called parsing ?

Is there a way of doing this easily & quickly for all the lines without
writing a macro?. I have no experience of macros & don't want to spend a lot
of time learning for what will probably be a one-off project.

Hope this makes sense.

Thanks for advice, hints & tips etc

KK

Mike

This is exactly what I was looking for, thanks for your help.

K


"Mike H" wrote in message
...
Hi,

You dont provide too much information but in principal parsing text is
fairly straightforward. Using the sentence:-

my name is MikeH and this is a sentence
=LEFT(A1,FIND("MikeH",A1,1)+5)
extracts 'my name is MikeH'

This formula
=MID(A1,FIND("MikeH",A1,1),999)
extracts 'MikeH and this is a sentence'

and if you want a bit out of the middle
=MID(A1,FIND("MikeH",A1,1),5)
extracts 'MikeH'

Hope this gets you off in the right direction.

Mike

"KRK" wrote:

Hello,

I have a spreadsheet (excel 2007) with many ( a few hundred) lines of
text.
Each line is in a single cell (A1, A2, A3, ... ) and is quite long. I
want
to split the line into several chunks, with each chunk in a different
cell
on the same line. The chunk 'boundaries' can be identified by keywords or
characters, eg *, 'name'. I think this is called parsing ?

Is there a way of doing this easily & quickly for all the lines without
writing a macro?. I have no experience of macros & don't want to spend a
lot
of time learning for what will probably be a one-off project.

Hope this makes sense.

Thanks for advice, hints & tips etc

KK

You may be able to do it with DataText to Columns if you can define the type of
separator to split the "chunks" into cells.

i.e. comma or space or something similar.

You may have to insert a separator first by using EditReplace on your data to
change a common keyword to a character.

Note: a single letter can be a de-limiter. Just remember the letter would be
case-sensitive.


Gord Dibben MS Excel MVP


On Sat, 8 Mar 2008 12:31:35 -0000, "KRK"
wrote:

Hello,

I have a spreadsheet (excel 2007) with many ( a few hundred) lines of text.
Each line is in a single cell (A1, A2, A3, ... ) and is quite long. I want
to split the line into several chunks, with each chunk in a different cell
on the same line. The chunk 'boundaries' can be identified by keywords or
characters, eg *, 'name'. I think this is called parsing ?

Is there a way of doing this easily & quickly for all the lines without
writing a macro?. I have no experience of macros & don't want to spend a lot
of time learning for what will probably be a one-off project.

Hope this makes sense.

Thanks for advice, hints & tips etc

KK