Indented Headings in single column - need to extract to multiple columns

[brett24]

[h=2]Indented Headings in single column - need to extract to multiple columns[/h]

<ins id="aswift_2_expand" style="text-decoration: none; display: inline-table; border: none; height: 250px; margin: 0px; padding: 0px; position: relative; visibility: visible; width: 300px; background-color: transparent;"><ins id="aswift_2_anchor" style="text-decoration: none; display: block; border: none; height: 250px; margin: 0px; padding: 0px; position: relative; visibility: visible; width: 300px; background-color: transparent;">******** width="300" height="250" frameborder="0" marginwidth="0" marginheight="0" vspace="0" hspace="0" allowtransparency="true" scrolling="no" allowfullscreen="true" id="aswift_2" name="aswift_2" style="left: 0px; position: absolute; top: 0px;">*********></ins></ins>
I have a spreadsheet which pulls in data, however the headings and sub-headings come in the same column i.e. A and are indented, for example:

Column A

England

Apple

Brand A

Brand B

Pear

Brand A

Brand B

France

Apple

Brand A

Brand B

Spain

Strawberry

Brand A

<tbody>
</tbody>

I need to display this indented data in three separate columns

Country	Fruit	Brand
England	Apple	Brand A
England	Apple	Brand B
England	Pear	Brand A
England	Pear	Brand B
France	Apple	Brand A
France	Apple	Brand B
Spain	Strawberry	Brand A

<tbody>
</tbody>

This will allow me to pivot if required. For example heading 1 is country and this is indented by 1 space, heading 2 is Produce and this is indented by 3 spaces, heading type is indented by 5 spaces.

Is there a formula I can use to keep the original indented column data but also view in a a tabular format.

Happy to send over an example Excel file.

Thanks,
Brett​

 

[brett24]

Indented Headings in single column - need to extract to multiple columns

<ins id="aswift_2_expand" style="text-decoration: none; display: inline-table; border: none; height: 250px; margin: 0px; padding: 0px; position: relative; visibility: visible; width: 300px; background-color: transparent;"><ins id="aswift_2_anchor" style="text-decoration: none; display: block; border: none; height: 250px; margin: 0px; padding: 0px; position: relative; visibility: visible; width: 300px; background-color: transparent;">******** width="300" height="250" frameborder="0" marginwidth="0" marginheight="0" vspace="0" hspace="0" allowtransparency="true" scrolling="no" allowfullscreen="true" id="aswift_2" name="aswift_2" style="left: 0px; position: absolute; top: 0px;">*********></ins></ins>
I have a spreadsheet which pulls in data, however the headings and sub-headings come in the same column i.e. A and are indented, for example:

Column A

England

Apple

Brand A

Brand B

Pear

Brand A

Brand B

France

Apple

Brand A

Brand B

Spain

Strawberry

Brand A

<tbody>
</tbody>

I need to display this indented data in three separate columns

Country	Fruit	Brand
England	Apple	Brand A
England	Apple	Brand B
England	Pear	Brand A
England	Pear	Brand B
France	Apple	Brand A
France	Apple	Brand B
Spain	Strawberry	Brand A

<tbody>
</tbody>

This will allow me to pivot if required. For example heading 1 is country and this is indented by 1 space, heading 2 is Produce and this is indented by 3 spaces, heading type is indented by 5 spaces.

Is there a formula I can use to keep the original indented column data but also view in a a tabular format.

Happy to send over an example Excel file.

Thanks,
Brett​

Tried looking at the space of the first character but this is not easy either.