+ Reply to Thread
Results 1 to 6 of 6

After using the VBA cstr function, Excel keeps converting string values to number.

  1. #1
    Registered User
    Join Date
    07-05-2013
    Location
    pooville
    MS-Off Ver
    Excel 2003
    Posts
    11

    After using the VBA cstr function, Excel keeps converting string values to number.

    Hi all,

    I'm trying to write code that converts a range of numeric values to string values, e.g. the number 123.456 to the string 123.456. I have tried using the cstr function to do this, but it doesn't seem to work. I find that my converted values are still numeric. Suppose I have 20 numbers in the range A1:A20, and I'd like these to be converted to strings and placed in the range B1:B20. This is the code I am using:

    Please Login or Register  to view this content.
    I'd expect the range B1:B20 to contain the string equivalents of the numbers in A1:A20, but this is not the case. What I find is that B1:B20 is the same A1:A20, i.e. using the =isnumber worksheet function verifies that both columns have numeric values.

    Any idea what I am doing wrong?

    Many thanks,
    I.

  2. #2
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: After using the VBA cstr function, Excel keeps converting string values to number.

    Thats right, i've just run it, one column as number format, run the code and get the same values, but in General format

    you could prefix with a "'" or use the worksheetfunction.text() approach?
    Hope this helps

    Sometimes its best to start at the beginning and learn VBA & Excel.

    Please dont ask me to do your work for you, I learnt from Reading books, Recording, F1 and Google and like having all of this knowledge in my head for the next time i wish to do it, or wish to tweak it.
    Available for remote consultancy work PM me

  3. #3
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,606

    Re: After using the VBA cstr function, Excel keeps converting string values to number.

    try formatting cells as text before assigning:
    Please Login or Register  to view this content.
    it will be formatted as text but isnumeric will anyway return frue.
    Best Regards,

    Kaper

  4. #4
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: After using the VBA cstr function, Excel keeps converting string values to number.

    Why not this......
    Please Login or Register  to view this content.
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  5. #5
    Registered User
    Join Date
    07-05-2013
    Location
    pooville
    MS-Off Ver
    Excel 2003
    Posts
    11

    Thumbs up Re: After using the VBA cstr function, Excel keeps converting string values to number.

    Hi all,

    Many thanks to all of you for the help.

    I went for Kaper's suggestion, and it works fine, does exactly what I want it to. I can test that I have the right data type by using the worksheet functions =isnumber and =istext.

    Thanks again, really do appreciate it!
    I.

    Dunno if I'm supposed to somehow mark this thread as "solved."

  6. #6
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,606

    Re: After using the VBA cstr function, Excel keeps converting string values to number.

    As described in http://www.excelforum.com/forum-rule...rum-rules.html :
    9. Acknowledge the responses you receive, good or bad. If your problem is solved, please say so clearly, and mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved". Or click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Dropdown option or Edit button will not appear -- ask a moderator to mark it.

    Would be also nice if you add some roeputation by clicking small star-shaped icons in lowerleft corners of the post(s) to those who helped.

+ 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. Issue converting string to number..
    By cummins in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-08-2011, 04:07 PM
  2. Converting text string to number string
    By cdworin in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 09-18-2010, 06:31 AM
  3. converting number string into time
    By paintballlovr in forum Excel General
    Replies: 12
    Last Post: 07-15-2010, 04:52 PM
  4. Converting string of letters to Number
    By sebkavam in forum Excel General
    Replies: 6
    Last Post: 07-26-2006, 05:55 PM
  5. [SOLVED] converting number string to number with decimal
    By rortiz in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-15-2005, 04:05 PM

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