+ Reply to Thread
Results 1 to 8 of 8

Problem with indata mixture of text and numbers

  1. #1
    Registered User
    Join Date
    07-16-2013
    Location
    Sweden
    MS-Off Ver
    Excel 2010
    Posts
    36

    Problem with indata mixture of text and numbers

    Hi
    I am working with an excel sheet that gets its data from a database, that I cannot affect the output from.
    Everything would be easy if the data just been correctly formatted from the beginning, but it isn't.

    The database sends everything out in some sort of text, and also the cells that appear blank/empty isn't. Normally it is just to convert it to numbers, but sometimes people miss to do that or miss some number, and keeps working.
    I have tried to make a formula that would be able to warn if people they have missed something but so far i have failed.

    As can be seen in the workbook the data is in one sheet and the calculations is done in another. In this case I have already converted most numbers but left some of the errors.
    E.g. cell N3 in Indata needs to be converted to numbers, and N7 changed to 10.
    What I want is that until that is done it will say "Lacking or incorrect information in N column in data sheet" in G1 and G3 in the calculation sheet.

    Anyone know anyway to make that happen?

    Thanks in advance.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,953

    Re: Problem with indata mixture of text and numbers

    You can run this macro.

    Please Login or Register  to view this content.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Registered User
    Join Date
    07-16-2013
    Location
    Sweden
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Problem with indata mixture of text and numbers

    Thank you very much oeldere. Will try it out.

  4. #4
    Registered User
    Join Date
    07-16-2013
    Location
    Sweden
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Problem with indata mixture of text and numbers

    Hi
    I have been trying it out and it work good aslong as it is a whole number.
    But as soon as it is decimal in it like 3.5 it becomes 35
    I am using . as a decimal seperator, dont know if that has anything to do with it?

  5. #5
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,953

    Re: Problem with indata mixture of text and numbers

    please add an excel file, with your problem, so we can test it.

  6. #6
    Registered User
    Join Date
    07-16-2013
    Location
    Sweden
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Problem with indata mixture of text and numbers

    Sorry that i didn't reply quicker.

    As can be seen in the file I get the problem for N9 and N10. In N9 it becomes 932 instead of 93.2 and N10 stays unconverted.
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,953

    Re: Problem with indata mixture of text and numbers

    In that case you could

    Select the whole column N.

    CTRL + H

    Find . (dot)
    Replace by , (komma)

    Replace all.

    The you get the result you want.

  8. #8
    Registered User
    Join Date
    07-16-2013
    Location
    Sweden
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Problem with indata mixture of text and numbers

    Thank you for the help.

    It turned out like this in the end:
    Please Login or Register  to view this content.
    Thank you for all the help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Problem with spaces between text and numbers
    By hensoros in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-19-2014, 10:36 AM
  2. Problem with text & numbers
    By TicklyTigger in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-24-2014, 01:28 PM
  3. Replies: 12
    Last Post: 05-04-2013, 08:24 AM
  4. mixture of do while and for loops
    By marko3 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-18-2011, 09:13 AM
  5. [SOLVED] Text & Numbers problem
    By Trying to excel in life but need help in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-06-2005, 01:06 AM

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