VBA convert numbers stored as text

Hello,

I will frequently have data that gets exported from an external source download to an excel file. I have created a separate excel file which has the formatting and layout that I want, that I copy and paste the exported data into.

The issue is that the data from the exported file is stored as text, I need my separate excel file to automatically convert the numbers stored as text in column F into numbers(just like if I selected the cells and hit the error button and click convert to numbers.)

There will always be some entries which are mixed numbers and letters, but that is ok, I just need to figure out a way to automate the function that occurs when I select all the cells in column F of the table and click "convert to number"

I have tried a few and a couple of ways were close to working but they either had an error, or made excel start acting funny.

The only thing I got to work was to automatically change the format of column F cells to number format....but that didnt change the format of the stored text entry.

Im attaching a sample worksheet incase anyone has any ideas.

Thanks.

Have you tried writing 1 (one) in a cell, copy that cell, then select the entire column to format and do a Paste Special... / select operation Multiple / OK.

Originally Posted by rollis13

Have you tried writing 1 (one) in a cell, copy that cell, then select the entire column to format and do a Paste Special... / select operation Multiple / OK.

Hmm when I do that it "adds rows to the table" and shifts my formulas and throws it off. It also gets rid of the conditional formatting I have in the file that im pasting into...which I now realize I didnt include in my sample work book.

My apologies.

Try setting the range equal to itself, e.g. something like

Please Login or Register  to view this content.

Originally Posted by rollis13

Didn't find any formulas in your data !

You can also try:
1) press F5, select Table13 and click OK
2) then from menu Data choose Text to Columns (all default options are ok) and click End

I know, my bad. I updated the file to reflect what im working with more. I cant show all the data in the table because reasons, but column F is the one with the issue. Formulas are located in column N.

Originally Posted by mgs73

Try setting the range equal to itself, e.g. something like

Please Login or Register  to view this content.

This is really close. It seems to work but it has some weird side effects.

This is what I put in VBA (based upon the latest sample workbook I just uploaded in the response to rollis)

Formula:

Please Login or Register  to view this content.

It seems to do what I asked, but it also makes excel glitchy(I think?) When I select a cell and am actively typing it shows up in a completely random cell for no apparent reason, but when I hit enter it does go to the proper cell.

Also, when I go to test the entries in column F with ISNUMBER or ISTEXT, it returns a zero rather than TRUE or FALSE.

Any idea why its doing those other things?

This seems to work, except if I clear the data in the table...then it gets an error an crashes.

Formula:

Please Login or Register  to view this content.

This worked, and adding the specified row numbers instead of the entire row got rid of the errors.

Thanks for the insight, Rollis your suggestion of Text to Columns put me on the right track, Thanks!

Formula:

Please Login or Register  to view this content.

Originally Posted by rollis13

But how many times must this formatting need to be done ? Can't it be done manually once every now and then, the goal is only four clicks away.

A macro with event triggering will execute at every cell change every few istants.
Anyway, what difference would this macro make:

Please Login or Register  to view this content.

The thing is, the file I'm working on is a tool that will need to be used by about 50 other people, most of which would rather reconcile by hand rather than learn those 4 clicks, so it needs to be as automated as possible.

Once every 2 weeks or so they will generate a report of new data, unique to the current time period, and paste into a blank table, therefore all the data they paste into the table will need to be converted.

After pasting the data, they will utilize filters and sorts to organize the data to the order that works for their location.

I will give your new macro a shot tomorrow to see how it behaves, definitely looks cleaner than what I have currently.

Well, instead of having the macro triggering hundreds of time a day (wasting energy) you could use event Worksheet_Activate that will trigger only when you select (activate) that specific sheet (or maybe never if you never move from one sheet to another).

I see what you are saying....and when looking into it it helped me understand why CTRL+Z wasn't working for this sheet....its because that Macro was running all the time. I get it.

Here is what I updated it to, I have a command button that pastes as values in the proper cell, so they have to push that to paste the data into the table, and I just tossed the command in with that so it only runs the one time when they put the data into the table.

Formula:

Please Login or Register  to view this content.

I am trying yours too, but it doesnt seem to do anything.

Formula:

Please Login or Register  to view this content.

Originally Posted by rollis13

It looks like you are pasting in column A but you have target column G in the first macro and column F in the other; which is the right column ?

Correct, sorry I forgot that I have added a new column since yesterday. Column F became column G.

Column G is where the data is that needs converting.

A5 is the top left corner of the table where the data values need to be pasted.

Originally Posted by rollis13

It looks like you are pasting in column A but you have target column G in the first macro and column F in the other; which is the right column ?

I working a bunch more bugs than just this one at the same time....my sincerest apologies for my brain fart when I tested your formula, while I did update the column on mine, I forgot to do it on yours.

Took a me a minute to see it, I just got too many windows and modules open at the same time lol.

Formula:

Please Login or Register  to view this content.

This does work beautifully.

Thanks again for the help.

Glad having been of some help .