Problem:
changing numeric values into text vales then adding a space into the text
cell, to take the place of the"," to denote thousands, move the "$" to the
end and change the "." to a comma, ex: $1,896,230.00 once changed to a text
value becomes "1896230.00", result I require is "1 896 230,00 $"

A number of Financial values across multiple spreadsheets require this type
of formatting. If this can be done as a macro or formula, that would be most
helpful.
--
Keith Dore

The only thing I can think of is a CASE statement in VBA.........what are the
largest and smallest "TEXT numbers" you would expect to enconter?

Vaya con Dios,
Chuck, CABGx3





"KDore@AIC" wrote:

Problem:
changing numeric values into text vales then adding a space into the text
cell, to take the place of the"," to denote thousands, move the "$" to the
end and change the "." to a comma, ex: $1,896,230.00 once changed to a text
value becomes "1896230.00", result I require is "1 896 230,00 $"

A number of Financial values across multiple spreadsheets require this type
of formatting. If this can be done as a macro or formula, that would be most
helpful.
--
Keith Dore

The largest numbers would be up to 100,000,000 and the smallest could be
negatives in the thousands. These are values taken from calculations of
investments, so there are growth and loss numbers given.
--
Keith Dore


"CLR" wrote:

The only thing I can think of is a CASE statement in VBA.........what are the
largest and smallest "TEXT numbers" you would expect to enconter?

Vaya con Dios,
Chuck, CABGx3





"KDore@AIC" wrote:

Problem:
changing numeric values into text vales then adding a space into the text
cell, to take the place of the"," to denote thousands, move the "$" to the
end and change the "." to a comma, ex: $1,896,230.00 once changed to a text
value becomes "1896230.00", result I require is "1 896 230,00 $"

A number of Financial values across multiple spreadsheets require this type
of formatting. If this can be done as a macro or formula, that would be most
helpful.
--
Keith Dore

If you could leave the number as a NUMBER, instead of converting it to TEXT,
and forgo the cents to 00, then a custom format like this might do
it..........

# ### ##0",00 $"

Vaya con Dios,
Chuck, CABGx3



"KDore@AIC" wrote:

The largest numbers would be up to 100,000,000 and the smallest could be
negatives in the thousands. These are values taken from calculations of
investments, so there are growth and loss numbers given.
--
Keith Dore


"CLR" wrote:

The only thing I can think of is a CASE statement in VBA.........what are the
largest and smallest "TEXT numbers" you would expect to enconter?

Vaya con Dios,
Chuck, CABGx3





"KDore@AIC" wrote:

Problem:
changing numeric values into text vales then adding a space into the text
cell, to take the place of the"," to denote thousands, move the "$" to the
end and change the "." to a comma, ex: $1,896,230.00 once changed to a text
value becomes "1896230.00", result I require is "1 896 230,00 $"

A number of Financial values across multiple spreadsheets require this type
of formatting. If this can be done as a macro or formula, that would be most
helpful.
--
Keith Dore

Only problem I have is that once I have the file re-formatted on a PC, it is
sent to Excel on a Mac which converts the formats back. Initially I had set
the International option so that this format was done by the system, issues
arose once transferred over to the Mac, these formats were then switched
back. So to avoid this, I was told to go with Text values instead, and so
the head ache begins.
--
Keith Dore


"CLR" wrote:

If you could leave the number as a NUMBER, instead of converting it to TEXT,
and forgo the cents to 00, then a custom format like this might do
it..........

# ### ##0",00 $"

Vaya con Dios,
Chuck, CABGx3



"KDore@AIC" wrote:

The largest numbers would be up to 100,000,000 and the smallest could be
negatives in the thousands. These are values taken from calculations of
investments, so there are growth and loss numbers given.
--
Keith Dore


"CLR" wrote:

The only thing I can think of is a CASE statement in VBA.........what are the
largest and smallest "TEXT numbers" you would expect to enconter?

Vaya con Dios,
Chuck, CABGx3





"KDore@AIC" wrote:

Problem:
changing numeric values into text vales then adding a space into the text
cell, to take the place of the"," to denote thousands, move the "$" to the
end and change the "." to a comma, ex: $1,896,230.00 once changed to a text
value becomes "1896230.00", result I require is "1 896 230,00 $"

A number of Financial values across multiple spreadsheets require this type
of formatting. If this can be done as a macro or formula, that would be most
helpful.
--
Keith Dore