Extracting from multiple lines of text in one cell.

[mrcrabby]

Hi this is my data in one cell contains.

Appointment Details
Provider:Roy
Service: SUPER
Customer Details:
Name: JOE
Phone: 07912345678
Email: blabla@blablabla.com
Address: 1600 Amphitheatre Parkway in Mountain View, Santa Clara County, California, United States

I need to take each of these points from this one cell and put it into individual cells under my headers in Excel.

Headers in Excel.
A1 Provider
B1 Service
C1 Name
D1 Phone
E1 Email
F1 Address

 

[mrcrabby]

I'm sure each line is separated by Char(10) new line.

 

[gallen]

It's awkward as we need to miss out the 2 headers (Appointment details and customer details). Are these 2 items always the 1st line and the 4th line as in your example?

If not will they always be "Appointment Details" and "Customer Details" with no chance of spelling error?

It's easy to split the string but the difficult part is only getting the items you want every time...

 

[FormR]

Hi, welcome to the forum!

Here is one option, the formula in A2 can be copied across to the other cells.

Excel 2013/2016
	A	B	C	D	E	F	G	H
1	Provider	Service	Name	Phone	Email	Address		Text
2	Roy	SUPER	JOE	07912345678	blabla@blablabla.com	1600 Amphitheatre Parkway in Mountain View, Santa Clara County, California, United States		Appointment Details Provider:Roy Service: SUPER Customer Details: Name: JOE Phone: 07912345678 Email: [EMAIL="blabla@blablabla.com"]blabla@blablabla.com[/EMAIL] Address: 1600 Amphitheatre Parkway in Mountain View, Santa Clara County, California, United States
Sheet1

Cell Formulas
Range	Formula
A2	=TRIM(LEFT(SUBSTITUTE(MID($H2,FIND(A1&":",$H2)+LEN(A1)+1,LEN($H2)),CHAR(10),REPT(" ",LEN($H2))),LEN($H2)))

 

[gallen]

Well that's a much better way than the VBA solution i was sorting.

 

[mrcrabby]

Hi, welcome to the forum!

Here is one option, the formula in A2 can be copied across to the other cells.

Excel 2013/2016

	A	B	C	D	E	F	G	H
Provider	Service	Name	Phone	Email	Address	Text
SUPER	JOE	07912345678	blabla@blablabla.com	1600 Amphitheatre Parkway in Mountain View, Santa Clara County, California, United States	Appointment Details Provider:Roy Service: SUPER Customer Details: Name: JOE Phone: 07912345678 Email: blabla@blablabla.com Address: 1600 Amphitheatre Parkway in Mountain View, Santa Clara County, California, United States

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFF00"]Roy[/TD]

[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A2[/TH]
[TD="align: left"]=TRIM(LEFT(SUBSTITUTE(MID($H2,FIND(A1&":",$H2)+LEN(A1)+1,LEN($H2)),CHAR(10),REPT(" ",LEN($H2))),LEN($H2)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Fantastic, thank you very much.