Menu

Convert Text Formated Numbers into Numbers all in VBA

Search this Thread

Prev

Previous Post

Next Post

Next

#4

Report Post

Posted to microsoft.public.excel.programming

Ron Rosenfeld Ron Rosenfeld is offline

Ron Rosenfeld is offline

external usenet poster

Posts: 5,651

Default

Convert Text Formated Numbers into Numbers all in VBA

On 6 Aug 2004 10:15:37 -0700, (Marston) wrote:

I just tested this - and unfortunately, it didn't work.
The numbers that were created as "text" stayed that way.

It worked properly here using numbers entered as text. There must be something
about your data of which I am not aware, or about the method you are using to
enter the subroutine.

Please post a sample of data that will not convert. And also, on a
non-converting value, post the output of the following:

With a non-converting data in A1:

=LEN(A1)

Then array enter the following formula:

=CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))

To *array-enter* a formula, hold down <ctrl<shift while hitting <enter. XL
will place braces {...} around the formula.

After you enter the array formula, place the cursor in the formula bar and
<F9. This should result in a highlighted set of numbers in the formula bar
looking something like: {32;49;32;160}

Hit <ctrl-C to copy that array, then paste it into your response to me.

Should I set c.Value = cDbl(c.Text)*1 ?

I don't know if that would work. But I would rather figure out why the
original is not working.

As a complicating factor - some of the items in the data are numbers
not text,
so I probably don't want to run this on those, just the items that are
numbers as text.

That should not be a problem. If non-numeric text is selected, the On Error
statement will cause the macro to just "skip over" those entries.

--ron

Reply With Quote

Reply With Quote

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Posting Rules
Smilies are On [IMG] code is On HTML code is Off Trackbacks are On Pingbacks are On Refbacks are On

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Text formated cells with numbers	Hugh self taught	New Users to Excel	3	August 18th 09 08:44 PM
Formula for extracting text formated numbers within ( )'s as numbe	M	Excel Discussion (Misc queries)	6	October 30th 08 11:59 PM
Convert numbers stored as text to numbers Excel 2000	Darlene	Excel Discussion (Misc queries)	6	January 31st 06 08:04 PM
Cells formated as numbers are calculating like text	MM_BAM	Excel Discussion (Misc queries)	4	July 7th 05 01:29 AM
How to convert Excel imported numbers from text to numbers?	Alden	Excel Discussion (Misc queries)	9	April 1st 05 09:51 PM

All times are GMT +1. The time now is 12:29 PM.

About Us

"It's about Microsoft Excel"

Contact Us ExcelBanter forum home Privacy Statement