Hello, I have a range of cells in my document where numbers are stored as text... I'd like to have some lines of VBA code to convert them to numbers so that I can use them in calculations. Could you please help me? (Excel 2007) Thank you in advance :)

VBA convert text to number : CCM

Réponse 1 / 6

pijaku Posted messages 13513 Registration date Status Modérateur Last intervention 2 771

Hello,
For example, to convert A1:A10 into Integers:

Sub test() Dim Array(), i As Long Array = Range("A1:A10") For i = LBound(Array, 1) To UBound(Array, 1) Array(i, 1) = CInt(Array(i, 1)) 'replace CInt with CDbl if decimal numbers Next Range("A1").Resize(UBound(Array, 1), 1) = Array End Sub

--
Best regards,
Franck P

fapfapfap

Thank you, the method works but only on the first column.

The range to be transformed in my case contains 2 columns...

Can you please indicate the solution again?

Thank you again for your invaluable help!

fapfapfap

Actually, it's fine, I'm just going to declare another variable and copy the same lines afterwards.

Thanks again!!

P.S.: I can't seem to mark the topic as resolved... if a mod could take care of it for me...

juguy

Hello pijaku, I tried your code by replacing A10 with A50000 since I have more than 30000 lines, and I get a type mismatch.
Do you have any idea?

pijaku Posted messages 13513 Registration date Status Modérateur Last intervention 2 771 > juguy

Hello,

In the empty lines, you are trying to convert "nothing" into an integer. That won’t work.
Focus on your last filled line...

Another idea, perhaps there is a value other than numbers among your 30,000 lines... A letter? An #N/A error? Something else?

Réponse 2 / 6

pijaku Posted messages 13513 Registration date Status Modérateur Last intervention 2 771

Well actually, no, with 1 variable you have enough...
For columns A and B:

Sub test() Dim Array(), i As Long Array = Range("A1:B10") For i = LBound(Array, 1) To UBound(Array, 1) Array(i, 1) = CInt(Array(i, 1)) 'replace CInt with CDbl for decimal numbers Array(i, 2) = CInt(Array(i, 2)) Next Range("A1").Resize(UBound(Array, 1), 2) = Array End Sub

--
Best regards,
Franck P

Réponse 3 / 6

chossette9 Posted messages 6855 Registration date Status Contributeur Last intervention 1 313

Hello,

no need for a macro:

1°) write 1 in a cell. Select the cell and copy it.
2°) Select the range of cells where the numbers are stored as text.
3°) Use the special paste - Multiply so that the text becomes a number.

Best regards.
--
That guy wasn't the brightest, as he was naive.

fapfapfap

thank you,

but actually I plan to integrate these lines into an existing macro...

I know your method but unfortunately, I really need to go through a macro

thank you

fapfapfap

I just tried the method and it doesn't work... I still have the little green triangle telling me that the cell contents are in text format.
I used your method in a macro:

Columns("M:N").Select
Selection.NumberFormat = "0.00"
Range("O3").Select
ActiveCell.FormulaR1C1 = "1"
Range("O3").Select
Selection.Copy
Range("M5:N277").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False
ActiveWindow.ScrollRow = 269
ActiveWindow.ScrollRow = 267
ActiveWindow.ScrollRow = 266
ActiveWindow.ScrollRow = 264
ActiveWindow.ScrollRow = 262
ActiveWindow.ScrollRow = 258
ActiveWindow.ScrollRow = 255
ActiveWindow.ScrollRow = 251
ActiveWindow.ScrollRow = 247
ActiveWindow.ScrollRow = 243
ActiveWindow.ScrollRow = 238
ActiveWindow.ScrollRow = 235
ActiveWindow.ScrollRow = 231
ActiveWindow.ScrollRow = 229
ActiveWindow.ScrollRow = 226
ActiveWindow.ScrollRow = 222
ActiveWindow.ScrollRow = 219

etc etc...

Where does the error come from please??

Thank you

Réponse 4 / 6

fapfapfap

A idea please!!!

White

Hello,

I have the same issue, manually using Paste Special converts the values to numbers but it doesn't work in VBA... Have you found a solution?

Thank you in advance,

Best regards.

ptimoun

'transformation of column b
Dim Tabl(), i As Long
Tabl = Range("B4:B33")
For i = LBound(Tabl, 1) To UBound(Tabl, 1)
Tabl(i, 1) = CInt(Tabl(i, 1))
Next
Range("B4").Resize(UBound(Tabl, 1), 1) = Tabl

'transformation of column C
Dim Tabl1(), j As Long
Tabl1 = Range("c4:c33")
For j = LBound(Tabl1, 1) To UBound(Tabl1, 1)
Tabl1(j, 1) = CInt(Tabl1(j, 1))
Next
Range("c4").Resize(UBound(Tabl1, 1), 1) = Tabl1

This is how I processed 2 columns

morjen

Thank you for everything
It shows me an error '13' at the level of Tabl(i, 1) = CInt(Tabl(i, 1))
What should I do?

Réponse 5 / 6

adenZZ

but for me it displays an error at Tabl(i, 1) = CInt(Tabl(i, 1))
I don't know if I need to change something?
thank you in advance

pijaku Posted messages 13513 Registration date Status Modérateur Last intervention 2 771

Hello,
What is the error message?

adenZZ

Thank you pijaku, I used Excel Direct with the Basic Convert method in the Data menu
have a nice day

simo > adenZZ

Yes exactly, it's the right solution.

Réponse 6 / 6

abousafia Posted messages 1 Status Membre

without using macros
the numbers in text format imported into table (a1:n300) in sheet1
in sheet2 a1=SUBSTITUTE(Sheet1!A1;",","."); copy the formula down to ...
in sheet3 a1=ROUND(Sheet2!A1;2); copy the formula down to ...
and the numbers in sheet3 are converted

sagessedz Posted messages 4 Status Membre

Hello
to solve this issue, you need to name the cells to convert, then use the following VBA code:
1 for the number format name it "Montant"
and here is the code

' convertir_TexteNombre Macro
Sub convertir_TexteNombre()
Range("Montant").Select
Selection.TextToColumns Destination:=Range("F2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
End Sub

2 For dates
' Convertir_TexteDate Macro

Sub Convertir_TexteDate()
Range("Dates").Select
Selection.TextToColumns Destination:=Range("H2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 4), TrailingMinusNumbers:=True
End Sub

Good luck

sagessedz Posted messages 4 Status Membre

Oh I forgot
2 for the date format named "Dates"

VBA convert text to number

6 réponses

Texture issues in enshrouded

Announcement: online source code archive

Pc that doesn't run normally

Keyboard issue with the "alt gr" key

End of the movie room 1408

Pc startup black screen white dash blinking

Edit mp3 file properties

Freebox telephony no dial tone

Pc won't start - pxe issue (e61 and m0f)

I would like to change the date on my digitec watch.