How to, in bulk, change Number Stored as Text? 4

[jsteph]

Hi all,
I'm assuming you're familiar with the "Number Stored as Text" problem...If not, in a nutshell I'm exporting data from MS Access and some currency values come over to Excel with this little error--but not all currency values have this--even some currency values *with comma's* come over correctly as numbers, but seemingly at random some currency columns will just decide it's a number stored as text.


I've googled this issue and the only thing I've found is to go to the Error Checking and there's a "Convert to Number" option. But this is a one-by-one thing--even if I make a selection or range it won't give me a "Convert All" button...you can imagine if there are a hundred columns with 20,000 rows this is going to take a while.

So:
1. Is there a trick to get either Access or Excel to export/import the data correctly? Again--in Access the field is a Currency type and some currency colunms come over fine, others just dont.

2. If I'm stuck with the export issue, how can I in one-button click (or maybe a few button-clicks) convert all of the many thousands of cells to their correct value.

3. I have tried to do the Format Cells and that does not work.
Thanks,
--Jim

 

[SkipVought]

Hi,

Enter a 1 in an empty cell.

Copy the cell.

Select ALL the "Numbers Stored as Text"

Edit > Paste Special - MULTIPLY

VOLA y'all!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jsteph]

Skip,
Well, it works, thanks!
--Jim

 

[AnotherHiggins]

I know this isn't the VBA forum, but I recognize your name, jsteph, and I see that you've answered questions in forum707 before. So I'll go ahead and post this solution here in the Office forum because it has saved me so much time in dealing with situations like the one you're in.

In fact, in Excel 2003 I had the following code assigned to a button on a custom toolbar. Now in 2007 I have it as part of a custom Addin that is available to me in the Quick Access Toolbar.

Sub ConvertTextAndFormulasToNumbers()
    '   Check to see if selection is formatted as Text -
    '   If so, then reformat selection as General
    '**********************************************************
    If Selection.NumberFormat = "@" Then _
            Selection.NumberFormat = "General"
            
    '   Replace formulas and numeric strings with real numbers
    '**********************************************************
    Selection.Value = Selection.Value
End Sub

I'm pretty sure I got the idea of using ".value = .value" on an entire selection (rather than looping through individual cells) from Skip. But it doesn't work when the cell is formatted as text, so I first check that and reformat if needed.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[jsteph]

John,
That looks like it'll work, but I'm not well versed in Excel and when I add that macro, it seems to only attach it to the workbook I'm in at that second. I reopen Excel and the macro's gone...though pushing the custom button opens up the prior sheet to which the macro appears to be attached.

So...and I'm sorry to get too far on a tangent but this is somewhat related...how do I make a library of functions such as that, that will open up whenever I open the instance of Excel on my machine, regardless of what workbook I'm in?
Thanks,
--Jim

 

[AnotherHiggins]

I think we should move our discussion over to the other forum if we go any deeper.... But the simplest way is probably to put the macro in your PERSONAL.XLS (.XLSB if you're in 2007) and assign a keyboard shortcut.

If you're not sure if you have a personal.xls file, then from excel, [ul]
[li]Press [Alt]+[F11] to open the VBEditor[/li]
[li]Press [Ctrl]+[R] to ensure that the process explorer is open on the left side[/li][ul]
[li]Process Explorer looks a bit like windows explorer[/li][/ul]
[li]Look for VBAProject (PERSONAL.XLS) in the process exlorer[/li][/ul]
If it is NOT there, then you can start to record a new macro. In the Store macro in field, select Personal Macro Workbook. Immediately after pressing OK you can Stop Recording. You'll now see VBAProject (PERSONAL.XLS) in the process exlorer. You can delete the code that was generated.

Once you see VBAProject (PERSONAL.XLS) in the process exlorer, then [ul]
[li]Right Click on VBAProject (PERSONAL.XLS)[/li]
[li]Select Insert > Module[/li][ul]
[li]If you just recorded a macro, then you can use that module instead of inserting a new one[/li][/ul]
[li]Paste in the code I posted[/li][/ul]

To assign a keyboard shortcut:[ul]
[li]In Excel 2003, go to Tools > Macro > Macros[/li]
[li]In Excel 2007, go to the Developer Ribbon > Code > Macros[/li]
[li]Select ConvertTextAndFormulasToNumbers[/li]
[li]Select Options[/li]
[li]Choose a Keyboard Shortcut[/li][ul]
[li]Be sure to choose a key combination that isn't already assigned!! TIP: you're less likely to interfere with any other keyboard shortcuts if you use [Ctrl] [highlight]+ [Shift][/highlight] + something rather than just ctrl + something.[/li][/ul][/ul]

When closing Excel, respond that you DO want to save changes to PERSONAL.XLS.

The PERSONAL.XLS file will default to being a hidden workbook so you won't see it every time you open Excel.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[jsteph]

John,
Thanks very much!
--Jim

 

[kjv1611]

Thanks to Skip and anotherhiggins for the posts.

I've just been using a custom macro to date that did somewhat the same thing, but I think it wasn't quite as smooth - I set it up to loop through all cells in every sheet of the active workbook, and just activate the cell, and then go to the next.... same as you click on it and hit Enter, and then let Excel do it's "magic." It's worked in all my circumstances so far, but I usually have to go in and edit the code a little for each instance.

Your two methods seem like they'll work faster and not require any ongoing code edits - I like that A LOT!

--

"If to err is human, then I must be some kind of human!" -Me

 

[Salut39]

Hi John,

I tried your macro on dates and it gives me incorrect results, basicly it converts some text 01/04/09 to 04/01/09 date.

Yuri

 

[AnotherHiggins]

That's very odd. I can't replicate that behavior.

Let's face it - there really isn't much to the macro. It just replaces whatever is in the cell with the value of whatever is in the cell.

It should be the same result that you'd get with the good old 'times 1 fix'. Does it?

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[jsteph]

Yuri,
That sounds like the Windows Regional settings...if text is placed in a cell and Excel did not format it the first time...ie just assumed it was some numbers with some dashes...the upon the Multiply * 1 operation it may now see it as a date and apply it's regional date format. Just a hunch...
--Jim

 

[kjv1611]

Yuri,

For your sort of change, it'd have to be either a regional setting, as jsteph stated, or just a date format setting on those cells. Perhaps to steer clear of that issue, you can also include some code that handles the format.

--

"If to err is human, then I must be some kind of human!" -Me

 

[Salut39]

Format seems to be the right one, I ussualy use =VALUE(A1) to convert to Dates. I am always up for simplifing processes, but maybe code coverting to General originally set to US format? But thanks anyway, it works with the other numbers.

Yuri