Convert Number To Text

Hi,
Can anyone help me with this problem?

I have a column of data with mixture of text and number.
I want to convert the whole row to text instead to mixture.

I know in excel, to make a number to a text you have to add a ' infront of the number.

Secondly in a particular worksheet, how to i write a code to change the whole activecell data to convert to text?



Can anyone help me?


Thank alot

ActiveCell.Value = "'" & ActiveCell.Value

--
Regards,
Tom Ogilvy


"brucelim80" <[email protected]> wrote
in message news:[email protected]...
>
> Hi,
> Can anyone help me with this problem?
>
> I have a column of data with mixture of text and number.
> I want to convert the whole row to text instead to mixture.
>
> I know in excel, to make a number to a text you have to add a ' infront
> of the number.
>
> Secondly in a particular worksheet, how to i write a code to change the
> whole activecell data to convert to text?
>
>
>
> Can anyone help me?
>
>
> Thank alot
>
>
> --
> brucelim80
> ------------------------------------------------------------------------
> brucelim80's Profile:
http://www.excelforum.com/member.php...o&userid=32244
> View this thread: http://www.excelforum.com/showthread...hreadid=521302
>

thank alot for your help

hi,

how do i write a code to select all rows from the same column and check for numeric data and convert them to the string

For example

Columns(G:G).Select

.....

Hi Bruce,

Try:

'=============>>
Public Sub Tester02()

Dim rng As Range

Set rng = Columns("G:G").SpecialCells(xlConstants, xlNumbers)

rng.NumberFormat = "@"

End Sub
'<<=============


---
Regards,
Norman



"brucelim80" <[email protected]> wrote
in message news:[email protected]...
>
> hi,
>
> how do i write a code to select all rows from the same column and check
> for numeric data and convert them to the string
>
> For example
>
> Columns(G:G).Select
>
> ....
>
>
> --
> brucelim80
> ------------------------------------------------------------------------
> brucelim80's Profile:
> http://www.excelforum.com/member.php...o&userid=32244
> View this thread: http://www.excelforum.com/showthread...hreadid=521302
>

Hi Bruce,

More robust would be:

'=============>>
Public Sub Tester02A()

Dim rng As Range

On Error Resume Next
Set rng = Columns("G:G").SpecialCells(xlConstants, xlNumbers)
On Error GoTo 0

rng.NumberFormat = "@"

End Sub
'<<=============


---
Regards,
Norman

Hi Bruce,

Additionally, replace:

> rng.NumberFormat = "@"


with

If Not rng Is Nothing Then rng.NumberFormat = "@"

--
---
Regards,
Norman



"Norman Jones" <[email protected]> wrote in message
news:[email protected]...
> Hi Bruce,
>
> More robust would be:
>
> '=============>>
> Public Sub Tester02A()
>
> Dim rng As Range
>
> On Error Resume Next
> Set rng = Columns("G:G").SpecialCells(xlConstants, xlNumbers)
> On Error GoTo 0
>
> rng.NumberFormat = "@"
>
> End Sub
> '<<=============
>
>
> ---
> Regards,
> Norman

hi Norman,

Thank for the great help.
I need another flavour from you.

How do i write a code? Such that it will automatically dectected the last row of the column and perform a check on individual Cell's data. I have tried using the method (range) that you had posted earlier witha for loop to extracted the value, but it can't work.


Can you help me please?

Thank you

Hi Bruce,

Try something like:

'=============>>
Public Sub Tester()
Dim LastCell As Range
Const col As String = "A" '<<==== CHANGE

Set LastCell = Cells(Rows.Count, col).End(xlUp)

MsgBox LastCell.Value

End Sub
'<<=============

Change A to the column of inteest.


---
Regards,
Norman



"brucelim80" <[email protected]> wrote
in message news:[email protected]...
>
> hi Norman,
>
> Thank for the great help.
> I need another flavour from you.
>
> How do i write a code? Such that it will automatically dectected the
> last row of the column and perform a check on individual Cell's data. I
> have tried using the method (range) that you had posted earlier witha
> for loop to extracted the value, but it can't work.
>
>
> Can you help me please?
>
> Thank you
>
>
> --
> brucelim80
> ------------------------------------------------------------------------
> brucelim80's Profile:
> http://www.excelforum.com/member.php...o&userid=32244
> View this thread: http://www.excelforum.com/showthread...hreadid=521302
>

Hi Bruce,

> I tired the code as given. It work fine if my column data is number.
> However, if my column data is Text then it will give a mismatch type
> error.

I do not understand. Show the exact code you are using.

> The code will detect the last cell data instead of the lastrow number.
> Norman, can you help me again ?

> For example my last row cell of the column contain the value "england"
> LastCell = "england"

Try:

'=============>>
Public Sub Tester2()
Dim LastCell As Range
Dim LastRow As Long
Dim LastValue As Variant
Const col As String = "A" '<<==== CHANGE

Set LastCell = Cells(Rows.Count, col).End(xlUp)

LastRow = LastCell.Row
LastValue = LastCell.Value

MsgBox "The last populated cell in column " _
& col & " is " & LastCell.Address(0, 0) _
& vbNewLine & "The corresponding row number is " _
& LastRow & vbNewLine _
& "The cell's value is " & LastValue

End Sub
'<<=============


---
Regards,
Norman



"brucelim80" <[email protected]> wrote
in message news:[email protected]...
>
> Hi norman,
> Thank for your help.
>
> I tired the code as given. It work fine if my column data is number.
> However, if my column data is Text then it will give a mismatch type
> error.
>
>
> The code will detect the last cell data instead of the lastrow number.
> Norman, can you help me again ?
>
> For example my last row cell of the column contain the value "england"
> LastCell = "england"
>
>
>
> Thank alot
>
>
> --
> brucelim80
> ------------------------------------------------------------------------
> brucelim80's Profile:
> http://www.excelforum.com/member.php...o&userid=32244
> View this thread: http://www.excelforum.com/showthread...hreadid=521302
>

Sorry to interrupt. Came across this Developersdex forum
somehow via Google while looking for way to convert
numbers (actually, dollar sums) to text.

Can anyone help me to write a simple macro (VB6/Word 2003/WinXP Pro SP2)
to convert the sum in a Word
Table 'Totals' cell to words?

The current macro I pieced together sums the right column
plus calculates tax and enters the total. Then it jumps directly
2 cells to left into a long row with room to enter the total
in 'words/text'. Currently it just ends there by highlighting
some dummy text in the row, ready for manually typing in the
figure.

If the number total is, for example, $150.60, I would manually
type in "One Hundred fifty and 60/100". However, the conversion need
not necessarily include the 60/100 (cents).
This latter could be typed in manually, as long as the macro ignores the
numbers to right of decimal point in the sum cell of the Word Table.

Perhaps I should post this in a new thread. I just registered here and
not sure yet how to do things. Saw this thread and thought it was as
close to what I'll ever get.

Appreciate any help at all on the subject. Am not a programmer; just an
old dabbler with quite a bit of experience in DOS batch files and just a
tad of VisBasic.

Jed



*** Sent via Developersdex http://www.developersdex.com ***