+ Reply to Thread
Results 1 to 8 of 8

Check if cellvalue is text or number, even when number is in textformat

  1. #1
    Forum Contributor
    Join Date
    09-21-2009
    Location
    Belgium - Mechelen
    MS-Off Ver
    Office 365
    Posts
    274

    Check if cellvalue is text or number, even when number is in textformat

    Hi there ,

    I need to determine if a cellvalue is text or a number , but all cellvalues have the textformat.
    ie :

    "1500"
    "AIRPORT"
    "WHSE"
    "800"
    "450"

    I need to check if the value ie "450" is a number and the check whether it is greater or equal to a certain number .
    I always encounter an error "type mismatch" if the cellvalue is a textvalue like "WHSE" or "AIRPORT"
    Please Login or Register  to view this content.
    How can i solve this pls ?

    thanks for helping me out.
    Mario

  2. #2
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Check if cellvalue is text or number, even when number is in textformat

    Please Login or Register  to view this content.
    or use Val
    Please Login or Register  to view this content.
    it's the cdbl that gives the error when you pass text to it.
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  3. #3
    Forum Contributor
    Join Date
    09-21-2009
    Location
    Belgium - Mechelen
    MS-Off Ver
    Office 365
    Posts
    274

    Re: Check if cellvalue is text or number, even when number is in textformat

    Hello JosephP ,
    Thanks for your reply , but i think it does not do what i want.
    Maybe i need to explain differently.

    In an excelsheet we receive , data ( text or numbers ) are stored as text , as you can see in the attached picture TextExcel.jpg.
    In column B the data is stored with a green upper left corner , which indicates that data is stored as text.

    Now in my code i try to find out if these values are numbers ( even stored as text ) or the values are real text values.

    Please Login or Register  to view this content.
    I've tried to change the cellformat with this code , but when running the code it still gives me "450" ie for the value 450 , and i need 450 as a number to be able to continue processing the code.

    Please Login or Register  to view this content.
    Any advice is welcome.

    TextExcel.jpg

    Many thanks
    Mario

  4. #4
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Check if cellvalue is text or number, even when number is in textformat

    Quote Originally Posted by MarMo View Post
    Any advice is welcome.
    a workbook would be more helpful than a picture. ;-)

  5. #5
    Forum Contributor
    Join Date
    09-21-2009
    Location
    Belgium - Mechelen
    MS-Off Ver
    Office 365
    Posts
    274

    Re: Check if cellvalue is text or number, even when number is in textformat

    Hi JosephP

    As requested i uploaded a smaller version a my file, but for security reasons i also deleted some data .
    You'll see that in column C there are text as number values.

    DumpTest.xlsx

    Mario

  6. #6
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Check if cellvalue is text or number, even when number is in textformat

    I really need to see the actual processing code too since your code works with that sample to return VoertuigType as a Variant/Double. if you need to actually alter the cell contents you need to change the format and then put the value back in the cell.

  7. #7
    Forum Contributor
    Join Date
    09-21-2009
    Location
    Belgium - Mechelen
    MS-Off Ver
    Office 365
    Posts
    274

    Re: Check if cellvalue is text or number, even when number is in textformat

    JosephP ,

    Thanks for your patience.
    I figured out what to do , but it might not be good programming.
    I actually , as you also suggested , reassign the value to the cell by using the following code :

    Please Login or Register  to view this content.
    Thank you for your time.
    If you have another approach to reassign the value , pls let me know.

    Kind regards
    Mario

  8. #8
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Check if cellvalue is text or number, even when number is in textformat

    that's fine if you do need to change the cell itself. your variable should contain a number anyway (unless you declared it as a String!)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1