Convert numbers to text with VBA

[DavidH]

Hi All,

I’m writing a procedure to convert numbers to text because I use accounting data downloaded to Excel from our accounting system, and sometimes the 5-digit account numbers come over as text and other times they download as numbers. I need a routine that will make them consistent so that they’ll work in vlookup functions.

I came across the following macro that seems to do the job, but I don’t understand the cell.Value = CStr(Temp) line; what is CStr?

Also, some of the procedures I came across prefixed the cell contents with an apostrophe. What’s the difference between setting a cell’s value to text like in the procedure below and prefixing it with an apostrophe?

Thanks for the help!

Sub NumToText()
' Convert the selected Excel numbers value to Excel text by changing format
For Each cell In Selection
If Not IsEmpty(cell.Value) And IsNumeric(cell.Value) Then
Dim Temp As Double
Temp = cell.Value
cell.ClearContents
cell.NumberFormat = "@"
cell.Value = CStr(Temp)
End If
Next cell
End Sub

 

[parry]

Hi David CSTR means convert to a string. A string is a term for text. While in the VBA Editor you can select Help from the menu to get more help on various methods. You can also select the key word you want to find out about and press F1 and it goes straight to the VBA Help for that method.

In the example you gave it means convert the text in the variable Temp (which is the current cell value). The difference between @ and ' to determine text ... err dunno


_________________
cheers
Parry
This message was edited by parry on 2002-12-01 19:01

 

[DavidH]

Hi Parry,

Thanks, tried the help and found cstr, should have thought of it before I posted.

The numbers in our accounting system that I need to convert to text are in this format: "11601.52010" where "11601" is the company number,"." is a delimiter, and "52010" is the account number. When I run this macro "52010" gets converted to "5201" (trailing zero is dropped); any suggestions on how I can get it to convert to the text "52010"?


Sub NumToText()
' Convert the selected Excel numbers value to Excel text by changing format
For Each cell In Selection
If Not IsEmpty(cell.Value) And IsNumeric(cell.Value) Then
Dim Temp As Double
Temp = cell.Value
cell.ClearContents
cell.NumberFormat = "@"
cell.Value = CStr(Temp)
End If
Next cell
End Sub

Thanks again for your help!

David

 

[Yogi Anand]

Hi David:

Assuming that your Company Code is 5 character long, then as far as ensuring that the Account Number portion is maintained to be 5 characters, you can add the following line after Cell.value = CStr(Temp)<PRE>

If Len(cell.Value)< 11 Then cell.Value = Left(cell.Value + "00000", 11)
</PRE>

_________________
Regards!
Yogi Anand
This message was edited by Yogi Anand on 2002-12-02 23:35

 

[DavidH]

Oops, I didn't mention that the company code ranges from 4 to 6 characters long, but the account code is always 5 characters.

David

 

[PanamaJoe]

Hi Parry,

Thanks, tried the help and found cstr, should have thought of it before I posted.

The numbers in our accounting system that I need to convert to text are in this format: "11601.52010" where "11601" is the company number,"." is a delimiter, and "52010" is the account number. When I run this macro "52010" gets converted to "5201" (trailing zero is dropped); any suggestions on how I can get it to convert to the text "52010"?


Sub NumToText()
' Convert the selected Excel numbers value to Excel text by changing format
For Each cell In Selection
If Not IsEmpty(cell.Value) And IsNumeric(cell.Value) Then
Dim Temp As Double
Temp = cell.Value
cell.ClearContents
cell.NumberFormat = "@"
cell.Value = CStr(Temp)
End If
Next cell
End Sub

Thanks again for your help!

David

This is a great post. I finally found what I was trying to do. I wanted to sort a group of columns and use a one click ICON with the Macro. This allowed the sum of a row (CountA=4), in my case, place it in the cells that needed the information and proceed to sort. Here is what I have....

Sub SelectSort()
'
' Sorts all the current month budget data
' Completed by Joe Redd 1-11-14
'
'
'ActiveCell.Range("P21")

Range("P14").Select

x = Range("P14")

Z = CStr(x) ' This was the key to my success of converting the cell value to "xyz" information...
' This then allowed the addition of the Chr() to be added to the Z value and set the sort
' operation.... w and then x. This all took about two days to figure out. //...

x = Chr(79) + Z ' x Adds selected number value to column O

w = Chr(78) + Z ' w Adds selected number value to column N

Range("P23").Select

ActiveCell(P23) = x

Range("P24").Select

ActiveCell(P24) = w

'===================================

Range("H5", x).Select ' Select the sort area

'Start the sort

ActiveWorkbook.Worksheets("Bill Sheet").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Bill Sheet").Sort.SortFields.Add Key:=Range( _
"N5", w), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Bill Sheet").Sort
.SetRange Range("H5", x)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("P15").Select
End Sub

There may be other ways to do what I did, but I don't get into this very much.

PanamaJoe

•