+ Reply to Thread
Results 1 to 8 of 8

To auto convert number to text. pls help.

  1. #1
    Registered User
    Join Date
    06-23-2014
    Location
    NA
    MS-Off Ver
    2010
    Posts
    5

    To auto convert number to text. pls help.

    Anyone know how to convert number/text to text/number?
    example
    a=1
    b=3
    c=5

    Let's say
    1) coloumn A1 value is "35", any formula/macro can auto convert to "bc" at B1?
    2) coloumn A1 value is "ab", any formula/macro can auto convert to "13" at B1?

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: To auto convert number to text. pls help.

    A way to go could be:

    Make a table with the letters.

    After that use VLookup to find those letters.

    b1=
    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
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    18,067

    Re: To auto convert number to text. pls help.

    Please Login or Register  to view this content.

  4. #4
    Forum Contributor
    Join Date
    02-16-2012
    Location
    London, England
    MS-Off Ver
    Excel mac 2011
    Posts
    238

    Re: To auto convert number to text. pls help.

    You can also use ascii codes - 65 I think it is should give what you are after. Here is a function that will convert a word into a number based on adding the ascii values of each letter.
    Please Login or Register  to view this content.
    The -64 just converts A to 1 instead of 65 Which is it's ascii Value, google Ascci table to see what value is assigned to which keyboard character.

    Hope this helps

    Rob

  5. #5
    Forum Contributor
    Join Date
    02-16-2012
    Location
    London, England
    MS-Off Ver
    Excel mac 2011
    Posts
    238

    Re: To auto convert number to text. pls help.

    Sorry code blocks didn't work for some reason.
    Please Login or Register  to view this content.
    By the way, for this function to work, put it into the VB editor, and you can then use it as a formula "=ASCIINUM("cell where word is")

    Rob

  6. #6
    Valued Forum Contributor Sean Thomas's Avatar
    Join Date
    03-25-2012
    Location
    HerneBay, Kent, UK
    MS-Off Ver
    Excel 2007,2016
    Posts
    970

    Re: To auto convert number to text. pls help.

    another function that is easier to understand for the average user

    use in cell B1 = ChangeText(A1)
    Please Login or Register  to view this content.
    Last edited by Sean Thomas; 06-23-2014 at 07:15 AM.
    Regards
    Sean

    Please add to my reputation if you think i helped
    (click on the star below the post)
    Mark threads as "Solved" if you have your answer
    (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code:
    [code] Your code here [code]
    Please supply a workbook containing example Data:
    It makes its easier to answer your problem & saves time!

  7. #7
    Registered User
    Join Date
    06-23-2014
    Location
    NA
    MS-Off Ver
    2010
    Posts
    5

    Re: To auto convert number to text. pls help.

    Thanks Oeldere help.

  8. #8
    Registered User
    Join Date
    06-23-2014
    Location
    NA
    MS-Off Ver
    2010
    Posts
    5

    Re: To auto convert number to text. pls help.

    Thank you for helping Jindon...

  9. #9
    Registered User
    Join Date
    06-23-2014
    Location
    NA
    MS-Off Ver
    2010
    Posts
    5

    Re: To auto convert number to text. pls help.

    Thanks Rob, thst's great VBA

  10. #10
    Registered User
    Join Date
    06-23-2014
    Location
    NA
    MS-Off Ver
    2010
    Posts
    5

    Re: To auto convert number to text. pls help.

    Quote Originally Posted by Sean Thomas View Post
    another function that is easier to understand for the average user

    use in cell B1 = ChangeText(A1)
    Please Login or Register  to view this content.
    ------------------------------------------
    Thank you Sean Thomas , this is more close what i needed. salute..

+ 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. Replies: 1
    Last Post: 10-28-2012, 05:42 AM
  2. Auto convert formula to text
    By mostafa_mov in forum Excel General
    Replies: 5
    Last Post: 10-03-2008, 08:42 AM
  3. AUTO CONVERT OF TEXT
    By Sandy in forum Excel General
    Replies: 2
    Last Post: 05-20-2006, 08:00 AM
  4. Convert a number formatted as text to a number in a macro
    By MACRE0 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-21-2005, 09:51 PM
  5. [SOLVED] convert text-format number to number in excel 2000%3f
    By Larry in forum Excel General
    Replies: 1
    Last Post: 07-29-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