Parsing text into Excel challenge

[mattc]

[ ... or "Baptism of our Lord Jesus Christ Festival of Code"]
Declaration: this is a work problem. I do not work for charidee or the NHS. You could spend your time more virtuously answering anyone else's question.

I have a PDF of some requirements from a Gov agency, and we want it in 2 columns of an Excel sheet. So the output will look like

14.5.6.7.2.1  <new cell> The thing must not explode in public.
14.5.6.7.2.1.1  <new cell> It must understand Welsh
14.5.6.7.2.1.2  <new cell> It shall not be pink or yellow.

Here is sometext in the input PDF's format:

Code: [Select]

6.2   A person who contravenes the requirement in regulation 3 commits an offence.

6.2.1    A person who obstructs, without reasonable [F1excuse], any person carrying out a function under these Regulations commits an offence.

6.2.2     A person who, without reasonable excuse, contravenes a direction given under regulation 5(2) or regulation 5(5) commits an offence.

6.3 An offence under this regulation is punishable on summary conviction by a fine.

6.4 Section 24 of the Police and Criminal Evidence Act 1984 F2 applies in relation to an offence under this regulation as if the reasons in subsection (5) of that section included—

   6.4.1  to maintain public health;
   6.4.2  to maintain public order.     

6.5 it's an offence to have any fun
We *ONLY* care about the first number clause ID: the rest is - to us - free text.

Go! (and thanks for reading x)

[hatler]

Use FIND to find whatever the space character is after the number block.

Then use MID(cell ref,1,number found above-1). That will give you the number part
Then use MID(cell ref,number found above+1,200). That will return the text part.

[mattc]

Use FIND to find whatever the space character is after the number block.

Then use MID(cell ref,1,number found above-1). That will give you the number part
Then use MID(cell ref,number found above+1,200). That will return the text part.

Thanks!

I did know this was possible - what's the easiest way to automate it? Can I just embed the FIND and the MID into 1 formula?

[hatler]

You can. Hold on.

[hatler]

If data in cell A1 is

1.1.1.2   Text to parse

Cell B1 formula is
=MID(A1,1,FIND(" ",A1,1)-1)

Cell C1 formula is
=MID(A1,FIND(" ",A1,1),200)

That is assuming the space after the number is a blank space.

C1 will however return leading spaces if there is more than one space in the original text, so use TRIM

=TRIM(MID(A1,FIND(" ",A1,1),200))

Sorry, edited.

[mattc]

Mr Hatler, you are a proper gent.

x

p.s. I won't be doing this until at least 0800, so you didn't have to rush

[hatler]

Nae worries.  If I'd waited until tomorrow morning then : -

a)  I wouldn't have been awake at 8am and
b)  Work would have started and I wouldn't have surfaced until gone 6pm.

[hatler]

It might be that the space after the number block is a tab, in which case you would have to copy that 'space' and paste that into the empty quote marks in the formulae (I think that would work).

Failing that, use Word to do the find and replace thing first.

[Pingu]

I think the CLEAN function will deal with tabs.

[hatler]

If the space character is a Tab then it may well paste into separate cells in Excel anyway. But good call if it's not a space and it doesn't split out.

[Mr Larrington]

I think the CLEAN function will deal with tabs.

(Asks Mr Google)

Oh, I say!  That could be rather useful to this Unit.  Ta!

[Lightning Phil]

If Excel 2010 or later there is a data —> text to columns menu option.

You can pick space, tab, semi colon, comma, or any other character as the delimiters.

[Jaded]

Which is OK if the character you want to slip the text at doesn't occur elsewhere in the text where you don't want to split it. Spaces often occur elsewhere.

Tabs, not so much, and sometimes Excel looks at a Tab and splits the data there anyway, when importing it.

[DaveReading]

If Excel 2010 or later there is a data —> text to columns menu option.

You can pick space, tab, semi colon, comma, or any other character as the delimiters.

That functionality goes back at least to Excel 2000.

[freeflow]

Might be simpler to import the pdf into Word to process the text then export to Excel.  All could be done within Execl VBA is required.  Do you have a sample pdf to have a play with.