Convert Number To Text

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

brucelim80 - 11 Mar 2006 03:06 GMT

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

Signature

brucelim80

Reply to this Message

Tom Ogilvy - 11 Mar 2006 03:28 GMT

ActiveCell.Value = "'" & ActiveCell.Value

Signature

Regards,
Tom Ogilvy

> Hi,
> Can anyone help me with this problem?
[quoted text clipped - 11 lines]
>
> Thank alot

Reply to this Message

brucelim80 - 13 Mar 2006 02:45 GMT

thank alot for your help

Signature

brucelim80

Reply to this Message

brucelim80 - 13 Mar 2006 08:17 GMT

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

....

Signature

brucelim80

Reply to this Message

Norman Jones - 13 Mar 2006 08:47 GMT

Hi Bruce,

Try:

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

Dim rng As Range

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

rng.NumberFormat = "@"

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

---
Regards,
Norman

> hi,
>
[quoted text clipped - 6 lines]
>
> ....

Reply to this Message

Norman Jones - 13 Mar 2006 08:51 GMT

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

Reply to this Message

Norman Jones - 13 Mar 2006 08:56 GMT

Hi Bruce,

Additionally, replace:

> rng.NumberFormat = "@"

with

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

Signature

---
Regards,
Norman

> Hi Bruce,
>
[quoted text clipped - 17 lines]
> Regards,
> Norman

Reply to this Message

brucelim80 - 14 Mar 2006 04:15 GMT

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

Signature

brucelim80

Reply to this Message

Norman Jones - 14 Mar 2006 05:07 GMT

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

> hi Norman,
>
[quoted text clipped - 9 lines]
>
> Thank you

Reply to this Message

brucelim80 - 15 Mar 2006 10:12 GMT

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

Signature

brucelim80

Reply to this Message

Norman Jones - 16 Mar 2006 11:26 GMT

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

> Hi norman,
> Thank for your help.
[quoted text clipped - 10 lines]
>
> Thank alot

Reply to this Message

Jed Timmer - 26 Mar 2006 09:59 GMT

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

Reply to this Message