I'm trying to conver a number value to a strin ex. convert 10 to "10"
I tried
With Range("B2:B" & NewResizeRange)
.Value = Range("B2:B" & NewResizeRange).Value
.NumberFormat = "0" ' Or .NumberFormat = "@"
End With
But it us not converting correctly. Any ideas?
With Range("A1")
.NumberFormat = "@"
.Value = .Value
End With
This will make the green triangle for Number stored as text show up and mean that your cell will indeed hold a text value. However, when doing calculations using such a cell as a parameter for the formula, Excel will do some conversion by itself.
If you do not want to use NumberFormat, you could also do:
Range("A1").value = "=""" & Range("A1").value & """" - This creates a formula (If the value was 10, it'll make a formula ="10") that forces the value to evaluate to a string.
Try selecting your range first. So:
With Range("B2:B" & NewResizeRange)
.Value = Range("B2:B" & NewResizeRange).Value
.Select
.Selection.NumberFormat = "@"
End With
Hope this helps.
Select in Excel VBA. Even if Select was the correct solution here, .Value.Select is nonsense.
Convert Integer into String in Excel VBA for Indian Currency in Rupees
Paste this code in macro by creating a module
Function name is ConvertIndianCurrency
Function ConvertIndianCurrency(ByVal MyNumber)
Dim Temp
Dim Count, DecimalPlace
ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Lakh "
Place(4) = " Crore "
' convert MyNumber to a string, trimming extra spaces.
MyNumber = Trim(Str(MyNumber))
' Find decimal place.
DecimalPlace = InStr(MyNumber, ".")
' If we find decimal place...
If DecimalPlace > 0 Then
ConvertIndianCurrency = "Please Remove Decimal Places in the Number..."
Exit Function
End If
Count = 1
If MyNumber <> "" Then
' convert last 3 digits of MyNumber to Indian Rupees.
Temp = ConvertHundreds(Right(MyNumber, 3))
If Temp <> "" Then
Rupees = Temp
End If
If Len(MyNumber) > 3 Then
' Remove last 3 digits from MyNumber.
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
End If
Do While MyNumber <> ""
Count = Count + 1
Temp = ConvertTwoDigits(Right(MyNumber, 2))
If Temp <> "" Then
Rupees = Temp & Place(Count) & Rupees
End If
If Len(MyNumber) > 2 Then
' Remove last 2 converted digits from MyNumber.
MyNumber = Left(MyNumber, Len(MyNumber) - 2)
Else
MyNumber = ""
End If
Loop
' Clean up Rupees.
Select Case Rupees
Case ""
Rupees = "Rupees Nil"
Case "One"
Rupees = "Rupee One Only"
Case Else
Rupees = "Rupees " & Rupees & " Only"
End Select
ConvertIndianCurrency = Rupees
End Function
Private Function ConvertHundreds(ByVal MyNumber)
Dim Result As String
' Exit if there is nothing to convert.
If Val(MyNumber) = 0 Then
Exit Function
End If
' Append leading zeros to number.
MyNumber = Right("000" & MyNumber, 3)
' Do we have a hundreds place digit to convert?
If Left(MyNumber, 1) <> "0" Then
Result = ConvertDigit(Left(MyNumber, 1)) & " Hundred "
End If
' Do we have a tens place to convert?
If Mid(MyNumber, 2) <> "0" Then
Result = Result & ConvertTens(Mid(MyNumber, 2))
Else
' If not, then convert the ones place digit.
Result = Result & ConvertDigit(Mid(MyNumber, 3))
End If
ConvertHundreds = Trim(Result)
End Function
Private Function ConvertTwoDigits(ByVal MyNumber)
Dim Result As String
' Exit if there is nothing to convert.
If Val(MyNumber) = 0 Then
Exit Function
End If
' Append leading zeros to number.
MyNumber = Right("00" & MyNumber, 2)
' Do we have a tens place to convert?
If Left(MyNumber, 1) <> "0" Then
Result = ConvertTens(Mid(MyNumber, 1))
Else
Result = Result & ConvertDigit(Right(MyNumber, 1))
End If
ConvertTwoDigits = Trim(Result)
End Function
Private Function ConvertTens(ByVal MyTens)
Dim Result As String
' Is value between 10 and 19?
If Val(Left(MyTens, 1)) = 1 Then
Select Case Val(MyTens)
Case 10: Result = "Ten"
Case 11: Result = "Eleven"
Case 12: Result = "Twelve"
Case 13: Result = "Thirteen"
Case 14: Result = "Fourteen"
Case 15: Result = "Fifteen"
Case 16: Result = "Sixteen"
Case 17: Result = "Seventeen"
Case 18: Result = "Eighteen"
Case 19: Result = "Nineteen"
Case Else
End Select
Else
' ... otherwise its between 20 and 99.
Select Case Val(Left(MyTens, 1))
Case 2: Result = "Twenty "
Case 3: Result = "Thirty "
Case 4: Result = "Forty "
Case 5: Result = "Fifty "
Case 6: Result = "Sixty "
Case 7: Result = "Seventy "
Case 8: Result = "Eighty "
Case 9: Result = "Ninety "
Case Else
End Select
' convert ones place digit.
Result = Result & ConvertDigit(Right(MyTens, 1))
End If
ConvertTens = Result
End Function
Private Function ConvertDigit(ByVal MyDigit)
Select Case Val(MyDigit)
Case 1: ConvertDigit = "One"
Case 2: ConvertDigit = "Two"
Case 3: ConvertDigit = "Three"
Case 4: ConvertDigit = "Four"
Case 5: ConvertDigit = "Five"
Case 6: ConvertDigit = "Six"
Case 7: ConvertDigit = "Seven"
Case 8: ConvertDigit = "Eight"
Case 9: ConvertDigit = "Nine"
Case Else: ConvertDigit = ""
End Select
End Function
Dim XN As Integer
Dim XS As String
XN = 10
XS = Trim(Str(XN)) 'now XS = "10"
=TEXT(B2;0)is invalid because the second argument ofTEXTis a format string. It is also invalid to try to make a formula that refers to its own cell (circular dependency).