I am currently working on a project where I have to prepare some data in an excel spreadsheet for upload to an external application which needs the text as html.
The bit I am stuck on is I have a simple table to prepare within One cell for upload into the application. I have been trying to do this using the concatenate function.
The source data is on another worksheet in the book and is in the format:
Queue Manager
Janette
01257 000000
Queue Manager
Paul
01257 000000
Queue Manager
Kevin
01257 000000
What I need to do is get all that data into 1 cell (showing as html code, because the application accepts bulk upload but only in the format specified. Unfortunately the format specified is HTML ad it expects to see it in one cell. So I am using the concatenate command within excel to bring together multiple values and my formula is :
=Concatenate(<TABLE border=0 cellPadding=5>
<TBODY>
<TR>
<TH>
<P align=left><FONT color=#006400 face=Arial size=1>Contact Role</FONT></P></TH>
<TH>
<P align=left><FONT color=#006400 face=Arial size=1>Contact Name</FONT></TH>
<TH>
<P align=left><FONT color=#006400 face=Arial size=1>VNET</FONT></TH>
<TH>
<P align=left><FONT color=#006400 face=Arial size=1>Full Telephone Number</FONT></TH>
<TH>
<P align=left><FONT color=#006400 face=Arial size=1>Mobile</FONT></TH>
<TH>
<P align=left><FONT color=#006400 face=Arial size=1>Mobex</FONT></P></TH></P></P>< /P></P></TR>
<TR>
<TD><FONT face=Arial size=1>='Marked Data Reformatted'!B12</FONT></TD>
<TD><FONT face=Arial size=1>='Marked Data Reformatted'!C12</FONT></TD>
<TD><FONT face=Arial size=1>='Marked Data Reformatted'!D12</FONT></TD>
<TD><FONT face=Arial size=1>='Marked Data Reformatted'!E12</FONT></TD>
<TD><FONT face=Arial size=1>='Marked Data Reformatted'!F12</FONT></TD>
<TD><FONT face=Arial size=1>='Marked Data Reformatted'!G12</FONT></TD></TR>)
What I want to see is the 'Marked Data Reformatted'!G12 fields with their actual values in and the html staying in tact but unfortunately Excel reads the html coding and gives me a result split over as many cells as I started with (shown below)
='Marked Data Reformatted'!B12 ='Marked Data Reformatted'!C12 ='Marked Data Reformatted'!D12 ='Marked Data Reformatted'!E12 ='Marked Data Reformatted'!F12 ='Marked Data Reformatted'!G12"
Can anyone help me with this problem?
The bit I am stuck on is I have a simple table to prepare within One cell for upload into the application. I have been trying to do this using the concatenate function.
The source data is on another worksheet in the book and is in the format:
Contact Role
Contact Name
Full Tel Number
Queue Manager
Janette
01257 000000
Queue Manager
Paul
01257 000000
Queue Manager
Kevin
01257 000000
What I need to do is get all that data into 1 cell (showing as html code, because the application accepts bulk upload but only in the format specified. Unfortunately the format specified is HTML ad it expects to see it in one cell. So I am using the concatenate command within excel to bring together multiple values and my formula is :
=Concatenate(<TABLE border=0 cellPadding=5>
<TBODY>
<TR>
<TH>
<P align=left><FONT color=#006400 face=Arial size=1>Contact Role</FONT></P></TH>
<TH>
<P align=left><FONT color=#006400 face=Arial size=1>Contact Name</FONT></TH>
<TH>
<P align=left><FONT color=#006400 face=Arial size=1>VNET</FONT></TH>
<TH>
<P align=left><FONT color=#006400 face=Arial size=1>Full Telephone Number</FONT></TH>
<TH>
<P align=left><FONT color=#006400 face=Arial size=1>Mobile</FONT></TH>
<TH>
<P align=left><FONT color=#006400 face=Arial size=1>Mobex</FONT></P></TH></P></P>< /P></P></TR>
<TR>
<TD><FONT face=Arial size=1>='Marked Data Reformatted'!B12</FONT></TD>
<TD><FONT face=Arial size=1>='Marked Data Reformatted'!C12</FONT></TD>
<TD><FONT face=Arial size=1>='Marked Data Reformatted'!D12</FONT></TD>
<TD><FONT face=Arial size=1>='Marked Data Reformatted'!E12</FONT></TD>
<TD><FONT face=Arial size=1>='Marked Data Reformatted'!F12</FONT></TD>
<TD><FONT face=Arial size=1>='Marked Data Reformatted'!G12</FONT></TD></TR>)
What I want to see is the 'Marked Data Reformatted'!G12 fields with their actual values in and the html staying in tact but unfortunately Excel reads the html coding and gives me a result split over as many cells as I started with (shown below)
Contact Role
Contact Name
VNET
Full Telephone Number
Mobile
Mobex
='Marked Data Reformatted'!B12 ='Marked Data Reformatted'!C12 ='Marked Data Reformatted'!D12 ='Marked Data Reformatted'!E12 ='Marked Data Reformatted'!F12 ='Marked Data Reformatted'!G12"
Can anyone help me with this problem?
Excel - Concatenate help required