locked
Numbers stored as text converted to numbers when assigned to an array in Excel RRS feed

  • Question

  • I have some numbers stored as text with (some) leading zeros (like 00123) and some terminating in decimal points (like 456.). When assigned to a VBA array (using the inbuilt VBA routine - arrResults = range(Results)) - with or without the .value,.value2, .text) they are all converted to numbers (123 and 456 in these examples. All the numbers stored as text are converted - and there are many of them.

    I am trying to clean up the "all text" values of an SQL database lookup table (number and text strings) which has errors (like 00123 should be 000123 and 456. should be 456 or the reverse). So having the Excel inbuilt VBA routine change text that looks like numbers to numbers is not helpful.

    The only alternative I've come across is to use range manipulations to create the lists and do the cleanup instead of transferring the ranges to arrays and manipulating them there. The cost is the range manipulation routine takes about two hours to the array manipulation routine's 15 minutes (lots of data in 50 workbooks).

    I know .NumberFormat doesn't help - a 456. text string can be stored in a General cell, or a Text cell and a 789 number can also be stored in a General cell but will be stored as text in a Text cell and so on.

    I'm using Office 2016.

    So, is there a way of stopping the inbuilt VBA range to array assignment routine from converting numbers stored as text to numbers? A flaw in the inbuilt VBA routine or have I missed an option setting somewhere in Excel?

    Regards

    Mikeph50




    • Edited by mikeph50 Sunday, February 18, 2018 12:51 AM improved grammar
    Saturday, February 17, 2018 7:59 AM

Answers

  • Thank you Jim

    Your right, I didn't investigate that very well, did I (rhetorical question)? I was clearly too focused on the outcome and didn't properly regard the steps.

    I completely neglected to look at the values in memory - that would have demonstrated that the inbuilt assign a range to array routine does not convert anything - text and numbers stored as text are accurately held in memory as text. My problem was at the other end, when assigning the array to the result range, where the natural behaviour of Excel is to convert numbers stored as text to numbers if the numberformat is not text. So I'm now using something like (with a sortAsText boolean):

    "Dim arrCalc()

    arrCalc = range(rngInput)

    do some calculations, including trimming the array

    if sortAsText = true then

          range(rngResult).nNumberFormat = "@"                     'Text format

    else

          range(rngResult).nNumberFormat = "General"            'text is text, numbers are numbers

    end if

    range(rngResult).resize(same size as array) = arrCalc"

    This routine works a lot faster then the same type of thing using range manipulations (20 minutes instead of an hour). So, I'm happy now.

    Thanks for your help. Much appreciated.

    Regards

    Mikeph50


    • Marked as answer by mikeph50 Wednesday, February 21, 2018 11:12 AM
    • Edited by mikeph50 Wednesday, February 21, 2018 11:15 AM error in text
    Tuesday, February 20, 2018 3:22 AM

All replies

  • Mike,
    re:  data changes

    I probably don't understand the question;  so the following works for me...
    '---
    '(code in sheet module)

    Sub JustTesting()
      Dim rng1 As Range
      Dim rng2 As Range
      Dim vArr As Variant

      Set rng1 = Me.Range("C3:C12")
      Set rng2 = Me.Range("E3:E12")
      rng2.NumberFormat = "@"
      vArr = rng1.Value

     ' change the array here

      rng2.Value = vArr
    End Sub
    '---

    '---
    Jim Cone
    https://goo.gl/IUQUN2  (dropbox)


    • Edited by James Cone Saturday, February 17, 2018 11:16 PM
    Saturday, February 17, 2018 4:53 PM
  • Hi Mikeph50,

    In advance, sorry if I misunderstand the issue.

    You can do what you want, by inserting/adding an apostrophe(') at the head of each cell.
    By adding an apostrophe as the first character, Excel recognize it a string (does not recognize as digit).

    If you provide your code, I will try to modify it.

    Regards,

    Ashidacchi

    Sunday, February 18, 2018 2:21 AM
  • Thank you Jim

    Your right, I didn't investigate that very well, did I (rhetorical question)? I was clearly too focused on the outcome and didn't properly regard the steps.

    I completely neglected to look at the values in memory - that would have demonstrated that the inbuilt assign a range to array routine does not convert anything - text and numbers stored as text are accurately held in memory as text. My problem was at the other end, when assigning the array to the result range, where the natural behaviour of Excel is to convert numbers stored as text to numbers if the numberformat is not text. So I'm now using something like (with a sortAsText boolean):

    "Dim arrCalc()

    arrCalc = range(rngInput)

    do some calculations, including trimming the array

    if sortAsText = true then

          range(rngResult).nNumberFormat = "@"                     'Text format

    else

          range(rngResult).nNumberFormat = "General"            'text is text, numbers are numbers

    end if

    range(rngResult).resize(same size as array) = arrCalc"

    This routine works a lot faster then the same type of thing using range manipulations (20 minutes instead of an hour). So, I'm happy now.

    Thanks for your help. Much appreciated.

    Regards

    Mikeph50


    • Marked as answer by mikeph50 Wednesday, February 21, 2018 11:12 AM
    • Edited by mikeph50 Wednesday, February 21, 2018 11:15 AM error in text
    Tuesday, February 20, 2018 3:22 AM
  • Ashidacchi

    Thanks for responding. Jim Cone put me on the right track (see my response to him).

    Regards

    Mikeph50

    Tuesday, February 20, 2018 3:24 AM