Unable to extract multiple data from a cell with many lines

[benetmc]

Hello,

Anyone know how to extract form like data from a cell in Excel? Thought my limited knowledge of VBA might be enough once I got started but was very wrong. Hours of reading message boards and no closer!

3 problems

• number of lines (carriage return's) in each cell is approx 25
• want to extract multiple fields - Name/Ref/address/number etc
• Data is not uniform - addresses are 4,5,or 6 lines which puts anything below/next to it out of sync

I managed to use the text to columns to separate by carriage return but as per point 3 above this was still out of sync across the columns. My VBA attempts were abysmal

I am unable to install any software/add-ons so apologies for the display below - the top three cells from my sheet.

Any help is appreciated - I have hundreds of these and hate manually doing things which I think should be automated. I have changed the personal details but hopefully you get the gist.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]
[TABLE="width: 605"]
<tbody>[TR]
[TD]Order number: 137218

Ordered by: Marty McFly

Delivery address:
Acme Products
The Future
Tomorrow Lane
Hill Valley
BK2 5TR
UK

Contact tel: 01999999992

Cost code: 127461

Items:
1 x 1MS640BN - Replacement Windows Phone (Blackberry Migrations only) FOOD @ £0.00 (Total: £0.00)
Total: £0.00

Tech Eval Answers:
Replacement Mobile Phones Section
Make of existing mobile phone? - Blackberry
Model of existing mobile phone? - Curve
Existing Mobile Phone Number? - 07777 123456
Is your mobile broken? - No
Is your mobile Lost? - No
Do you require a new sim? - No
Any special requirements? - N/A
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 605"]
<tbody>[TR]
[TD]Order number: 137278

Ordered by: Tony Stark

Delivery address:
House on a Cliff
Private Road
Newhouse
Malibu
ML4 5XU
US

Contact tel: 0789101112

Cost code: 127032

Items:
1 x 1MS640BN - Replacement Windows Phone (Blackberry Migrations only) FOOD @ £0.00 (Total: £0.00)
Total: £0.00

Tech Eval Answers:
Replacement Mobile Phones Section
Make of existing mobile phone? - blackberry
Model of existing mobile phone? - curve
Existing Mobile Phone Number? - 0789101112
Is your mobile broken? - No
Is your mobile Lost? - No
Do you require a new sim? - Yes
Any special requirements? - no
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 605"]
<tbody>[TR]
[TD]Order number: 137274

Ordered by: Indiana Jones

Delivery address:
University of Chicago
Archeogogy Department
Chicago
IJ1 5IS

Contact tel: 07779996661

Cost code: 127032

Items:
1 x 1MS640BN - Replacement Windows Phone (Blackberry Migrations only) FOOD @ £0.00 (Total: £0.00)
Total: £0.00

Tech Eval Answers:
Replacement Mobile Phones Section
Make of existing mobile phone? - Blackberry
Model of existing mobile phone? - 9320
Existing Mobile Phone Number? - 0778 201 304
Is your mobile broken? - Yes
Is your mobile Lost? - No
Do you require a new sim? - No
Any special requirements? - can my new phone be posted to 11, Temple of Doom, Doom St, Central America
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Roderick_E]

With a macro VBA solution digging through all this text, think about start and stop "triggers" within the text. For example, if the text always has "Ordered by:" we know this is that start trigger for the name. We know that immediately after this text we have the Name and that the name stops when we encounter "Delivery address:".
So we get the text between "Ordered by:" & "Delivery address:", we trim it and remove the carriage returns (vbcr). What remains is the name. We do this for all the elements we want to extract from this mess

 

[benetmc]

Thanks Roderick,

I was hoping this would be possible but still think it is probably outside of my ability to put something together.

It might be something I come back to as a test case when I finally get round to improving my VBA knowledge.

For now "time spent" - "time saved" is not looking very positive

 

[Peter_SSs]

Welcome to the MrExcel board!

Using a table similar to post #1, please post the expected outcome and layout for those particular samples & add any further information you can about how those results were obtained manually.

 

[István Hirsch]

Enter the bold strings in the first row, then the formula below in B2, then copy the formula across and down.

Excel Workbook
	A	B	C	D	E	F	G	H
1		Order number	Ordered by	Delivery address	Contact tel	Cost code	Items	Tech eval answers
2	Order number: 137218Ordered by: Marty McFlyDelivery address:Acme ProductsThe FutureTomorrow LaneHill ValleyBK2 5TRUKContact tel: 01999999992Cost code: 127461Items:1 x 1MS640BN - Replacement Windows Phone (Blackberry Migrations only) FOOD @ ?0.00 (Total: ?0.00)Total: ?0.00Tech Eval Answers:Replacement Mobile Phones SectionMake of existing mobile phone? - BlackberryModel of existing mobile phone? - CurveExisting Mobile Phone Number? - 07777 123456Is your mobile broken? - NoIs your mobile Lost? - NoDo you require a new sim? - NoAny special requirements? - N/A	137218	Marty McFly	Acme ProductsThe FutureTomorrow LaneHill ValleyBK2 5TRUK	01999999992	127461	1 x 1MS640BN - Replacement Windows Phone (Blackberry Migrations only) FOOD @ ?0.00 (Total: ?0.00)Total: ?0.00	Replacement Mobile Phones SectionMake of existing mobile phone? - BlackberryModel of existing mobile phone? - CurveExisting Mobile Phone Number? - 07777 123456Is your mobile broken? - NoIs your mobile Lost? - NoDo you require a new sim? - NoAny special requirements? - N/A
Sheet