How to convert numeric values in Words/text in EXCEL?

I have a spreadsheet into which a numeric table has been imported from a PDF file. All the numeric values in this table are showing as text entries now it's in EXCEL.
I could use the VALUE() function to convert all cells to Numeric Values, but just wondering if it's possible to run a small VBA routine to convert numeric numbers shown as text to values?

Say, the table is in cells A1:Z60.

Hi,

I have a table with several columns of characteristics from a given marketing campaign. Ultimately I would like to measure each individual description characteristic against campaign success rates. I have determined that I must convert the text entries describing each characteristic into a numeric value. This is where I'm stuck. Can anyone help with this?

Am trying to find a function that will convert a dollar amount to a text description, i.e. "$5.25" to "Five Dollars and 25/100" (quotes for emphasis only). Any ideas where I could find it??

Is there a way in VBA to find Date and Dollar values on imported data and set the appropriate formatting?

Using Excel 2003 with data from Access using either 1) Query -Tools, Office Links, Analyze with Excel or 2) the DoCmd.OutputTo acQuery… MicrosoftExcelBiff8(*.xls) from within Access

I am in the process of creating report specific macros’ for our teams users that do a lot of formatting on each query and turn it into a report, but am wondering if there is a better way. Now I create a macro to manually select and format the columns that have date values and the same for dollar values. I have to go through each report and code these columns in, and also go back and update with changes. Users can run these from an add-in any time they want against the imported data.

Is there a way to go through a workbook and find cells with dates and convert them if needed and set the date format? Most come up as dates but some as text. The two formats I start with are either d/mm/yy (as date or text) and dd-mmm-yy (as date or text). These I change to dd/mm/yyyy.

So far dollar values always seem to be as numbers in “#,##0.00;(#,##0.00)” format. and I just change the format of these to "$#,##0.00_);[Red]($#,##0.00)"

Here are samples of what I do now:

Code:

  Columns("E:AD").Select  
  Selection.ColumnWidth = 13.5  
  Selection.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)  
 

  Columns("W:Z").Select  
   Selection.NumberFormat = "mm/dd/yyyy;@"  
   Selection.HorizontalAlignment = xlCenter  
   Selection.ColumnWidth = 10.5"  

Dollars are always 13.5 and dates 10.5 users don’t like autofit as they want columns to look uniform

Thanks for suggestions!

I made an Excel function that converts a large string of numbers into their numeric word names. I am offering it to the community as free public domain.

It has many parameters to control the desired output. You can choose USA or European names with a simple parameter change. English names are used, however I designed this so that you can easily translate the English word to any other language--if anyone does this, send me a copy and I'll keep the code on my sites for others to use.

Here is a link to the workbook that includes the worksheet example, instructions of use, an a list of the official numeric names as a reference:

http://www.markmain.co/public/NumName.xlsm

The macro is found using Alt-F11 (after clicking the Enable macro button). Here are the instructions that I made for my macro:

Function NumName
by Mark D Main
version 1.0
Free for public domain use
Provided freely without any expressed warrenty--you assume all responsibility in using it.
Optional numType As Byte = 0, _
Optional decimalStr As String = ".", _
Optional currencyName As String, _
Optional oneCurrencyName As String, _
Optional fractionName As String, _
Optional oneFractionName As String, _
Optional fractionSize As Integer = -1, _
Optional fractionZeros As String = "x", _
Optional grammerType As Byte = 3) As String numText:
String; required field
The numric text of digits that will be translated by the function.
The function will delete all non-numeric characters and concatinate what is left into one long number; all currency symbols are removed and ignored. The only remaining characters will be digits and a single decimal point and/or minus sign (dash) to denote a negative number; this dash must be the first or last character used (excluding any leading or trailing spaces, which are automatically removed. numType:
0 or 1;
0 will use the USA names and 1 will use the European names (see "names" tab). decimalStr:
String; optional field
Typically a period or comma is used, but it can be represented by any character(s) string of any length. currencyName:
String; optional field
This curency name text is inserted between the whole numbers and the decimal fraction--just before the word "and" that connects these two pieces. It is best to enter this as lowercase and control it's capitlization with the grammerType parameter; however capitalizing here will force capitalization for grammerType values of 0 through 3. Blank turn this feature off. E.g. "dollars", "pounds" oneCurrencyName:
String; optional field
used for just 1 unit; e.g. one dollar. Will us currencyName if left blank. fractionName:
String; optional field
If used then this fractional curency name will be used on the far right of the text following the display of fraction presented in whole numbers; otherwise, if left blank, the fraction is simply displayed numerically.
Note: if a fraction name is used here then you must also provide a fractionSize > 0. oneFractionName:
String; optional field
used for just 1 unit; e.g. one cent. Will us fractionName if left blank.
See "Note" in fractionName above. fractionSize:
-1, 0, 1 or more; optional field
-1 = variable decimal size--display is based on the number of decimals provided
0 = fixed size of zero decimal places; no decimals are displayed; all decimal numbers are truncated--no rounding is performed
1 or more = fixed decimal places--the number declares the size; trailing zeros are padded if needed; any excess numbers are truncated--no rounding is performed; e.g. "2" would be used for "cents" because 1 cent uses 2 decimal places fractionZeros:
x, 0, z, n, nn or 0n; optional field
x = (default) only display fractions not equal to zero
0 = display all fractions with zero shown as "0"
0n = display all fractions with zero shown as "0"; and zero currency shown as "no"
z = display all fractions with zero shown as "zero"
n = display all fractions with zero shown as "no"
nn = display all fractions with zero shown as "no"; and zero currency shown as "no"
adding an asterisk "*" before or after any of these choices (e.g. x* or *nn) will display the decimals as sized to the fractionSize by padding leading zeros. grammerType:
0, 1, 2, 3, 4 or 5; optional field
0 = no grammer correction; lowercase "and"
1 = add commas between each 3-digit set; lowercase "and"
2 = add commas and hyphenate ten digit words; lowercase "and"
3 = (default) add commas and hyphenate ten digit words and uppercase first letter; lowercase "and"
4 = add commas and hyphenate ten digit words and uppercase first letter; uppercase "And"
5 = add commas and hyphenate ten digit words and uppercase for all letter
EXAMPLES:

This shows the text input and output using some parameters to produce typical dollars an cents output:

"00000" --> "Zero Dollars and No Cents"

"0" --> "Zero Dollars and No Cents"

"0.00" --> "Zero Dollars and No Cents"

"0000.1" --> "Zero Dollars and 10 Cents"

"0000.0123" --> "Zero Dollars and 01 Cent"

"000.023456789" --> "Zero Dollars and 02 Cents"

"000120" --> "One Hundred Twenty Dollars and No Cents"

"100" --> "One Hundred Dollars and No Cents"

"10" --> "Ten Dollars and No Cents"

"1" --> "One Dollar and No Cents"

"0100" --> "One Hundred Dollars and No Cents"

"1000000" --> "One Million Dollars and No Cents"

"159487326159487326159487326159487732615948732615974873261594873261594873261594873261594815948732615964873269798615948732 615948732615948732615948732615948732615948732615948732615948732615948732615948159487326159487326159487326159487326159487 326159487326159487326159487326159487326159487326159487326159487326.69" --> "One Hundred Fifty-Nine Centillion, Four Hundred Eighty-Seven Novemnonagintillion, Three Hundred Twenty-Six Octononagintillion, One Hundred Fifty-Nine Septnonagintillion, Four Hundred Eighty-Seven Sexnonagintillion, Three Hundred Twenty-Six Quinnonagintillion, One Hundred Fifty-Nine Quattuornonagintillion, Four Hundred Eighty-Seven Trenonagintillion, Three Hundred Twenty-Six Duononagintillion, One Hundred Fifty-Nine Unnonagintillion, Four Hundred Eighty-Seven Nonagintillion, Seven Hundred Thirty-Two Novemoctogintillion, Six Hundred Fifteen Octooctogintillion, Nine Hundred Fourty-Eight Septoctogintillion, Seven Hundred Thirty-Two Sexoctogintillion, Six Hundred Fifteen Quinoctogintillion, Nine Hundred Seventy-Four Quattuoroctogintillion, Eight Hundred Seventy-Three Treoctogintillion, Two Hundred Sixty-One Duooctogintillion, Five Hundred Ninety-Four Unoctogintillion, Eight Hundred Seventy-Three Octogintillion, Two Hundred Sixty-One Novemseptuagintillion, Five Hundred Ninety-Four Octoseptuagintillion, Eight Hundred Seventy-Three Septseptuagintillion, Two Hundred Sixty-One Sexseptuagintillion, Five Hundred Ninety-Four Quinseptuagintillion, Eight Hundred Seventy-Three Quattuorseptuagintillion, Two Hundred Sixty-One Treseptuagintillion, Five Hundred Ninety-Four Duoseptuagintillion, Eight Hundred Fifteen Unseptuagintillion, Nine Hundred Fourty-Eight Septuagintillion, Seven Hundred Thirty-Two Novemsexagintillion, Six Hundred Fifteen Octosexagintillion, Nine Hundred Sixty-Four Septsexagintillion, Eight Hundred Seventy-Three Sexsexagintillion, Two Hundred Sixty-Nine Quinsexagintillion, Seven Hundred Ninety-Eight Quattuorsexagintillion, Six Hundred Fifteen Tresexagintillion, Nine Hundred Fourty-Eight Duosexagintillion, Seven Hundred Thirty-Two Unsexagintillion, Six Hundred Fifteen Sexagintillion, Nine Hundred Fourty-Eight Novemquinquagintillion, Seven Hundred Thirty-Two Octoquinquagintillion, Six Hundred Fifteen Septquinquagintillion, Nine Hundred Fourty-Eight Sexquinquagintillion, Seven Hundred Thirty-Two Quinquinquagintillion, Six Hundred Fifteen Quattuorquinquagintillion, Nine Hundred Fourty-Eight Trequinquagintillion, Seven Hundred Thirty-Two Duoquinquagintillion, Six Hundred Fifteen Unquinquagintillion, Nine Hundred Fourty-Eight Quinquagintillion, Seven Hundred Thirty-Two Novemquardragintillion, Six Hundred Fifteen Octoquardragintillion, Nine Hundred Fourty-Eight Septquardragintillion, Seven Hundred Thirty-Two Sexquardragintillion, Six Hundred Fifteen Quinquardragintillion, Nine Hundred Fourty-Eight Quattuorquardragintillion, Seven Hundred Thirty-Two Trequardragintillion, Six Hundred Fifteen Duoquardragintillion, Nine Hundred Fourty-Eight Unquardragintillion, Seven Hundred Thirty-Two Quardragintillion, Six Hundred Fifteen Novemtrigintillion, Nine Hundred Fourty-Eight Octotrigintillion, Seven Hundred Thirty-Two Septtrigintillion, Six Hundred Fifteen Sextrigintillion, Nine Hundred Fourty-Eight Quintrigintillion, One Hundred Fifty-Nine Quattuortrigintillion, Four Hundred Eighty-Seven Tretrigintillion, Three Hundred Twenty-Six Duotrigintillion, One Hundred Fifty-Nine Untrigintillion, Four Hundred Eighty-Seven Trigintillion, Three Hundred Twenty-Six Novemvigintillion, One Hundred Fifty-Nine Octovigintillion, Four Hundred Eighty-Seven Septenvigintillion, Three Hundred Twenty-Six Sexvigintillion, One Hundred Fifty-Nine Quinvigintillion, Four Hundred Eighty-Seven Quattuorvigintillion, Three Hundred Twenty-Six Trevigintillion, One Hundred Fifty-Nine Duovigintillion, Four Hundred Eighty-Seven Unvigintillion, Three Hundred Twenty-Six Vigintillion, One Hundred Fifty-Nine Novemdecillion, Four Hundred Eighty-Seven Octodecillion, Three Hundred Twenty-Six Septendecillion, One Hundred Fifty-Nine Sexdecillion, Four Hundred Eighty-Seven Quindecillion, Three Hundred Twenty-Six Quattuordecillion, One Hundred Fifty-Nine Tredecillion, Four Hundred Eighty-Seven Duodecillion, Three Hundred Twenty-Six Undecillion, One Hundred Fifty-Nine Decillion, Four Hundred Eighty-Seven Nonillion, Three Hundred Twenty-Six Octillion, One Hundred Fifty-Nine Septillion, Four Hundred Eighty-Seven Sextillion, Three Hundred Twenty-Six Quintillion, One Hundred Fifty-Nine Quadrillion, Four Hundred Eighty-Seven Trillion, Three Hundred Twenty-Six Billion, One Hundred Fifty-Nine Million, Four Hundred Eighty-Seven Thousand, Three Hundred Twenty-Six Dollars and 69 Cents"

"1000000000000000000000000000000000000000000000.0100" --> "One Quattuordecillion Dollars and 01 Cent"

wondering if someone can help me out with a little problem im having. I am trying to convert a word to text using a VB I want it to do exactly what the =TEXT(02, "#,###,##0.00" ) does



to sum it up heres how im trying to do it, it wont let me put the extra "" around "#,###,##0.00"


Quote:

Converted1 = "=TEXT(" & Amount & " #,###,##0.00 & )" < ---- Amount is already set and working so thats not the prob


Worksheets("Sheet1").Cells(rowNum, yAmountCnvcord) = Converted1

End If
Next rowNum

End Sub



I would like to thank everyone on these forums for the help ive been given in the past.

Hello,

Need assistance with this... can't seem to understand the format cells portion. Can someone help.

Sample spreadsheet attached..

Thanks in advance.

French Macros would help too..

Hi. I copy data from *.dbf file to xls file using maco. After that fields are in text format. How can I convert data from text (ex. "123") to numeric format (ex. 123) using maco.