Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Converting Numbers to Strings.
You already know that you can use variables in your macros, and that there are two very basic types of variables: string variables (containing characters) and numeric variables (containing numeric values). You can quickly and easily convert a number into a string in your macros. This is the done with the Str() function. The way you use this function is as follows:
A = Str(B)
In this syntax, if B is equal to 5, then when completed, A will be " 5"; if B is -4, then A would be "-4". Notice the leading space when converting positive numbers. This may not provide satisfactory results for some subroutines. Instead, you should create a function that returns a stripped-down version of the string. The following function does just that:
Please Note:
This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: 
Function ToNum(X as Variant) as String
Dim A as String
A = Trim(Str(X))
ToNum = A
End Function
The reason that the value passed to the VBA function (X) is defined as a Variant is that you can then pass any type of numeric value.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2334) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Converting Numbers to Strings.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!
Need to normalize your data in some way so that all your values are in a given format? This tip presents a number of ...
Discover MoreUsing macros to step through each cell in a selection is a common occurrence. What if that selected range is made up of ...
Discover MoreYou can create macros that are automatically executed whenever certain events occur within a worksheet. This tip details ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2016-01-07 08:17:27
Michael (Micky) Avidan
@Upasana,
Try: =SUBSTITUTE(ADDRESS(1,COLUMN(),4),1,)
--------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2016)
ISRAEL
2016-01-07 04:06:32
Upasana
I want to convert column name e.g A to 1 and then back to A till the last column or say example "AB" to 28 and then back to "AB". Using chr and asc functions I am able to convert only single character names. Please help me to convert more than one character at a time in VBA for excel 2010.
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2021 Sharon Parq Associates, Inc.
Comments