logo elektroda
logo elektroda
X
logo elektroda
Advanced Search
logo elektroda

VBA Excel - "The number in this cell is formatted as text"

karola0921 4737 13
ADVERTISEMENT
Treść została przetłumaczona polish » english Zobacz oryginalną wersję tematu
Reply | New topic
  • #1 16214308
    karola0921
    Level 6  
    When importing data, some of them formatted after import are as text .
    There is no apostrophe there , a after double clicking on the cell, its content is shifted to the left and turns into a number .
    Or I can click on that exclamation point and choose convert to number. It has the same effect . (i.e. the number is converted )

    VBA Excel - "The number in this cell is formatted as text"

    Does anyone know how you can get such an effect from VBA?

    * I tried to record this convert but the recorder does not record anything only "select range".
    * I checked too formatting, is "numeric" (0.00)
  • ADVERTISEMENT
  • Helpful post
    #2 16214354
    JRV
    VBA, Excel specialist  
    What decimal separator in the system?
    Try to change the sheet "," to "."
  • ADVERTISEMENT
  • #3 16214485
    karola0921
    Level 6  
    JRV wrote:
    What decimal separator in the system?
    Try changing the ""," to "."


    strange it doesn't work but I made a mistake and saved it...yes

    Code: text Expand Select all Copy to clipboard
    rng(1).Replace ".", "."

    even more strange that there is no "." in the source file.

    Added after 6 [minutes]:



    Code: text Expand Select all Copy to clipboard
    rng(1).Replace ".", ","

    even more strange that there is no "." in the source file.

    sorry but it doesn't work.
  • #4 16214778
    Andie
    Level 22  
    It's best to attach a sample file that you want to import
  • ADVERTISEMENT
  • #5 16215046
    JRV
    VBA, Excel specialist  
    Andie wrote:
    sample file you want to import

    Or the first 4 ... 5 lines from it
  • ADVERTISEMENT
  • #7 16215106
    JRV
    VBA, Excel specialist  
    Andie wrote:
    sample file you want to import
    It's not the source one, it's already imported. I think they are imported from text, this one interests us.
    To the word in tom test.xls I see all excel values as numbers
  • #8 16215181
    karola0921
    Level 6  
    This is source.
    The file is saved by a proprietary program. It is a txt file but saved with the ".xls" extension due to a program error.
    How will you use the open
    Code: VBScript
    Log in, to see the code


    and then you copy the range to another file via VBA\ ("range.copy \\\ pastespecial")
    it will be the same effect as in the picture in the first post.

    To sum up.
    This happens when moving some values.
    Previously, I was using the "replace" method as you wrote.
    It's just different with it sometimes it throws me commas, so I don't do it anymore.
  • #9 16215239
    JRV
    VBA, Excel specialist  
    Right. text. At me excel opens it correctly, all as numbers,
    copies and paste xlValues the same correct (numbers)
    karola0921 wrote:
    range to another file by VBA \ ("range.copy \\\ pastespecial

    Perhaps in another file a text format (cells where pastespecial)?
  • #10 16215279
    karola0921
    Level 6  
    I checked on a blank sheet. (for office 2016)

    I don't know why but how do I "replace" and then
    Code: VBScript
    Log in, to see the code

    the problem disappears ...

    It works anyway ...
    I will check it at work and let you know. D
    Thank you for your help and commitment.
  • #11 16215688
    Andie
    Level 22  
    If the problem persists, you may need to change the file extension to txt before importing - and then import it as normal text, and at the import stage specify the correct data format (you can also use the appropriate code)

    andie
  • #12 16216730
    Maciej Gonet
    VBA, Excel specialist  
    If the cells on your worksheet are in General format then what you tried should work:
    Code: VB.net
    Log in, to see the code
    The period is the decimal separator in VBA, and Excel converts this automatically to numbers in local format.
  • #13 16217014
    karola0921
    Level 6  
    Andie wrote:
    If the problem persists, you may need to change the file extension to txt before importing - and then import it as normal text, and at the import stage specify the correct data format (you can also use the appropriate code)

    andie



    I've already tried there is some other method to change the extension than mine ?
    I do it through generate and run ".bat" file from within VBA so it gets access to cmd but at work, the antivirus is crazy about this: / (Especially with file operations)

    Although it's actually a new topic ... but if you have a simple recipe, write it. )
  • Helpful post
    #14 16217139
    clubs
    Level 38  
    Hello

    You can also multiply anything by 1
    = A1 * 1
Reply | New topic
Report a violation of the law

Topic summary

The discussion revolves around an issue in VBA Excel where imported data is formatted as text instead of numbers. Users describe the problem of numbers appearing left-aligned in cells, indicating they are treated as text. Solutions proposed include changing the decimal separator, using the Replace method, and ensuring the correct number format is applied. Suggestions also include importing the file as a text file and specifying the correct data format during the import process. A workaround mentioned is multiplying the text-formatted numbers by 1 to convert them to numeric values. The conversation highlights the challenges faced when dealing with files saved incorrectly and the need for proper formatting in Excel.
Summary generated by the language model.
ADVERTISEMENT