parsing text

[KRK]

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

 

[Ron Rosenfeld]

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

 

[Mike H]

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]

Mike

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

K

 

[Gord Dibben]

You may be able to do it with Data>Text 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 Edit>Replace 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