I am creating a text template in excel and I have the following 2
rows(combination of text, number and formula and %). please note that I
changed the format of E1 to percentage with 2 decimal.
To combine the following cells together:

A1 | B1 | C1| D1 | E1 | F1
2 | Divided by | 3 | is | =(A1/C1) | percent

i created this formula in cell A2:
=A1&B1&C1&D1&E1&F1

and i get:
2 Divided by 3 is: .666666666666 percent

and i want to get the following in one cell:
2 Divided by 3 is: 66.66% percent

what should i do?

Put this in E1
=ROUND(A1/C1,2)
--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


"Sam" wrote:

I am creating a text template in excel and I have the following 2
rows(combination of text, number and formula and %). please note that I
changed the format of E1 to percentage with 2 decimal.
To combine the following cells together:

A1 | B1 | C1| D1 | E1 | F1
2 | Divided by | 3 | is | =(A1/C1) | percent

i created this formula in cell A2:
=A1&B1&C1&D1&E1&F1

and i get:
2 Divided by 3 is: .666666666666 percent

and i want to get the following in one cell:
2 Divided by 3 is: 66.66% percent

what should i do?

=concatenate(A1," ",B1," ",C1," ",D1," ",round((E1*100),0)," ",F1)

Dave
--
Brevity is the soul of wit.


"Sam" wrote:

I am creating a text template in excel and I have the following 2
rows(combination of text, number and formula and %). please note that I
changed the format of E1 to percentage with 2 decimal.
To combine the following cells together:

A1 | B1 | C1| D1 | E1 | F1
2 | Divided by | 3 | is | =(A1/C1) | percent

i created this formula in cell A2:
=A1&B1&C1&D1&E1&F1

and i get:
2 Divided by 3 is: .666666666666 percent

and i want to get the following in one cell:
2 Divided by 3 is: 66.66% percent

what should i do?

Thanks alot.
now i have another problem, how can i keep the formatting of A1 or any
cell. for example if A1 is a bold red number with a comma in a
middle(<red<b5,333</b</red how can i keep the same format when i use
Concatenate?


"Dave F" wrote:

=concatenate(A1," ",B1," ",C1," ",D1," ",round((E1*100),0)," ",F1)

Dave
--
Brevity is the soul of wit.


"Sam" wrote:

I am creating a text template in excel and I have the following 2
rows(combination of text, number and formula and %). please note that I
changed the format of E1 to percentage with 2 decimal.
To combine the following cells together:

A1 | B1 | C1| D1 | E1 | F1
2 | Divided by | 3 | is | =(A1/C1) | percent

i created this formula in cell A2:
=A1&B1&C1&D1&E1&F1

and i get:
2 Divided by 3 is: .666666666666 percent

and i want to get the following in one cell:
2 Divided by 3 is: 66.66% percent

what should i do?

Cells with formulas don't support this kind of formatting.

Sam wrote:

Thanks alot.
now i have another problem, how can i keep the formatting of A1 or any
cell. for example if A1 is a bold red number with a comma in a
middle(<red<b5,333</b</red how can i keep the same format when i use
Concatenate?

"Dave F" wrote:

=concatenate(A1," ",B1," ",C1," ",D1," ",round((E1*100),0)," ",F1)

Dave
--
Brevity is the soul of wit.


"Sam" wrote:

I am creating a text template in excel and I have the following 2
rows(combination of text, number and formula and %). please note that I
changed the format of E1 to percentage with 2 decimal.
To combine the following cells together:

A1 | B1 | C1| D1 | E1 | F1
2 | Divided by | 3 | is | =(A1/C1) | percent

i created this formula in cell A2:
=A1&B1&C1&D1&E1&F1

and i get:
2 Divided by 3 is: .666666666666 percent

and i want to get the following in one cell:
2 Divided by 3 is: 66.66% percent

what should i do?


--

Dave Peterson

Sam

Your choice of two methods.

1. Get rid of F1 and use this formula =A1&B1&C1&D1&TEXT(E1,"#,##0.00%")

2. Change E1 to =(A1/C1)*100 then =A1&B1&C1&D1&TEXT(E1,"#,##0.00")&F1


Gord Dibben MS Excel MVP


On Thu, 18 Jan 2007 08:39:36 -0800, Sam wrote:

I am creating a text template in excel and I have the following 2
rows(combination of text, number and formula and %). please note that I
changed the format of E1 to percentage with 2 decimal.
To combine the following cells together:

A1 | B1 | C1| D1 | E1 | F1
2 | Divided by | 3 | is | =(A1/C1) | percent

i created this formula in cell A2:
=A1&B1&C1&D1&E1&F1

and i get:
2 Divided by 3 is: .666666666666 percent

and i want to get the following in one cell:
2 Divided by 3 is: 66.66% percent

what should i do?

what should i do to format a cell that has formula...?
can i change this cell content to a text format after i Concatenate?
my main goal is to automate an analysis sentence that contains percentage
and increase or decrease in it including their formats. do you know how
should i do it


"Dave Peterson" wrote:

Cells with formulas don't support this kind of formatting.

Sam wrote:

Thanks alot.
now i have another problem, how can i keep the formatting of A1 or any
cell. for example if A1 is a bold red number with a comma in a
middle(<red<b5,333</b</red how can i keep the same format when i use
Concatenate?

"Dave F" wrote:

=concatenate(A1," ",B1," ",C1," ",D1," ",round((E1*100),0)," ",F1)

Dave
--
Brevity is the soul of wit.


"Sam" wrote:

I am creating a text template in excel and I have the following 2
rows(combination of text, number and formula and %). please note that I
changed the format of E1 to percentage with 2 decimal.
To combine the following cells together:

A1 | B1 | C1| D1 | E1 | F1
2 | Divided by | 3 | is | =(A1/C1) | percent

i created this formula in cell A2:
=A1&B1&C1&D1&E1&F1

and i get:
2 Divided by 3 is: .666666666666 percent

and i want to get the following in one cell:
2 Divided by 3 is: 66.66% percent

what should i do?


--

Dave Peterson

You may be able to use a worksheet event that looks for changes in those cells
and then rebuilds the formatting.

But if the amount of formatting you want to keep is a lot (font size,
underlines, boldness, italics...) or if the number of cells that need this done
is a lot, it can get pretty messy pretty quickly.

Here's a thread that addressed this:

http://groups.google.co.uk/group/mic...e109bd6708 a7

or
http://snipurl.com/17sv6

Look at how JE McGimpsey and Ron Rosenfeld approached it.

Sam wrote:

what should i do to format a cell that has formula...?
can i change this cell content to a text format after i Concatenate?
my main goal is to automate an analysis sentence that contains percentage
and increase or decrease in it including their formats. do you know how
should i do it

"Dave Peterson" wrote:

Cells with formulas don't support this kind of formatting.

Sam wrote:

Thanks alot.
now i have another problem, how can i keep the formatting of A1 or any
cell. for example if A1 is a bold red number with a comma in a
middle(<red<b5,333</b</red how can i keep the same format when i use
Concatenate?

"Dave F" wrote:

=concatenate(A1," ",B1," ",C1," ",D1," ",round((E1*100),0)," ",F1)

Dave
--
Brevity is the soul of wit.


"Sam" wrote:

I am creating a text template in excel and I have the following 2
rows(combination of text, number and formula and %). please note that I
changed the format of E1 to percentage with 2 decimal.
To combine the following cells together:

A1 | B1 | C1| D1 | E1 | F1
2 | Divided by | 3 | is | =(A1/C1) | percent

i created this formula in cell A2:
=A1&B1&C1&D1&E1&F1

and i get:
2 Divided by 3 is: .666666666666 percent

and i want to get the following in one cell:
2 Divided by 3 is: 66.66% percent

what should i do?


--

Dave Peterson


--

Dave Peterson

One way:

Put this in your worksheet code module (right-click on the worksheet tab
and choose View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rSource As Range
Dim i As Long
Dim nPos As Long
Dim nLen As Long
Dim sTemp As String
Set rSource = Range("A1:F1")
If Not Intersect(Target, rSource) Is Nothing Then
For i = 1 To rSource.Cells.Count
sTemp = sTemp & " " & rSource(i).Text
Next i
With Range("A2")
On Error Resume Next
Application.EnableEvents = False
.Value = Mid(sTemp, 2)
Application.EnableEvents = True
On Error Resume Next
nPos = 1
For i = 1 To rSource.Count
nLen = Len(rSource(i).Text)
With .Characters(nPos, nLen).Font
.ColorIndex = rSource(i).Font.ColorIndex
.Bold = rSource(i).Bold
End With
nPos = nPos + nLen + 1
Next i
End With
End If
End Sub



In article ,
Sam wrote:

what should i do to format a cell that has formula...?
can i change this cell content to a text format after i Concatenate?
my main goal is to automate an analysis sentence that contains percentage
and increase or decrease in it including their formats. do you know how
should i do it


"Dave Peterson" wrote:

Cells with formulas don't support this kind of formatting.

Sam wrote:

Thanks alot.
now i have another problem, how can i keep the formatting of A1 or any
cell. for example if A1 is a bold red number with a comma in a
middle(<red<b5,333</b</red how can i keep the same format when i use
Concatenate?

"Dave F" wrote:

=concatenate(A1," ",B1," ",C1," ",D1," ",round((E1*100),0)," ",F1)

Dave
--
Brevity is the soul of wit.


"Sam" wrote:

I am creating a text template in excel and I have the following 2
rows(combination of text, number and formula and %). please note that
I
changed the format of E1 to percentage with 2 decimal.
To combine the following cells together:

A1 | B1 | C1| D1 | E1 | F1
2 | Divided by | 3 | is | =(A1/C1) | percent

i created this formula in cell A2:
=A1&B1&C1&D1&E1&F1

and i get:
2 Divided by 3 is: .666666666666 percent

and i want to get the following in one cell:
2 Divided by 3 is: 66.66% percent

what should i do?


--

Dave Peterson

Note, however, that changing the formats in the source range will not
trigger a Change event, so the destination cell won't change until a new
entry is made in the source range...

In article ,
JE McGimpsey wrote:

Put this in your worksheet code module (right-click on the worksheet tab
and choose View Code):

This solved my old problem. my new problem is when I concatenate in one cell
the data lose their format. i want the final result to be 2 devided by 3 is
66.66% percent but i want 66.66% to be bold and red! do you know the solution
to this answer.

"JE McGimpsey" wrote:

One way:

Put this in your worksheet code module (right-click on the worksheet tab
and choose View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rSource As Range
Dim i As Long
Dim nPos As Long
Dim nLen As Long
Dim sTemp As String
Set rSource = Range("A1:F1")
If Not Intersect(Target, rSource) Is Nothing Then
For i = 1 To rSource.Cells.Count
sTemp = sTemp & " " & rSource(i).Text
Next i
With Range("A2")
On Error Resume Next
Application.EnableEvents = False
.Value = Mid(sTemp, 2)
Application.EnableEvents = True
On Error Resume Next
nPos = 1
For i = 1 To rSource.Count
nLen = Len(rSource(i).Text)
With .Characters(nPos, nLen).Font
.ColorIndex = rSource(i).Font.ColorIndex
.Bold = rSource(i).Bold
End With
nPos = nPos + nLen + 1
Next i
End With
End If
End Sub



In article ,
Sam wrote:

what should i do to format a cell that has formula...?
can i change this cell content to a text format after i Concatenate?
my main goal is to automate an analysis sentence that contains percentage
and increase or decrease in it including their formats. do you know how
should i do it


"Dave Peterson" wrote:

Cells with formulas don't support this kind of formatting.

Sam wrote:

Thanks alot.
now i have another problem, how can i keep the formatting of A1 or any
cell. for example if A1 is a bold red number with a comma in a
middle(<red<b5,333</b</red how can i keep the same format when i use
Concatenate?

"Dave F" wrote:

=concatenate(A1," ",B1," ",C1," ",D1," ",round((E1*100),0)," ",F1)

Dave
--
Brevity is the soul of wit.


"Sam" wrote:

I am creating a text template in excel and I have the following 2
rows(combination of text, number and formula and %). please note that
I
changed the format of E1 to percentage with 2 decimal.
To combine the following cells together:

A1 | B1 | C1| D1 | E1 | F1
2 | Divided by | 3 | is | =(A1/C1) | percent

i created this formula in cell A2:
=A1&B1&C1&D1&E1&F1

and i get:
2 Divided by 3 is: .666666666666 percent

and i want to get the following in one cell:
2 Divided by 3 is: 66.66% percent

what should i do?


--

Dave Peterson

If you're using the code I gave you , just format the cell with the
calculation as bold and red.

In article ,
Sam wrote:

This solved my old problem. my new problem is when I concatenate in one cell
the data lose their format. i want the final result to be 2 devided by 3 is
66.66% percent but i want 66.66% to be bold and red! do you know the solution
to this answer.