TEXT TO NUMBER FORMAT

JLouis

Active Member
Joined
Jan 1, 2004
Messages
295
Office Version
  1. 365
Platform
  1. Windows
Hello. I have a back office database where I can export reports into excel. Unfortunately, the data on the reports are formatted as text within the sheet, making the information unusable for data manipulation using formulas and macros. Vlookups and other references return an "na" as a result of the formulas.

The data I want to use on the exported reports can be converted to numbers if selected and then choose "convert to numbers" but that defeats the purpose and doesn't allow for automation.

What can I do to convert the data of an entire sheet from text to number format using a macro or other technique?

Thank you.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
How many columns need to be converted?
You can use "Text to Columns" on each column to convert them to numbers (just select the column, go to "Text to Columns" from the Data menu, and click Finish.
Do this on each of the columns that you need converted.
If you turn on the Macro Recorder before performing these steps manually, you will get the VBA code you need to do this automatically.
 
Upvote 0
Thank you for the reply. Through my searches for a solution I have tried this technique with some success. The data moves around on the report so it was difficult getting a consistent result.

I was hoping there was something out there that could apply to a whole sheet so it wouldn't matter where the data ended up. I'll play around with it some more.

Thanks again JOE4.
 
Upvote 0
I was hoping there was something out there that could apply to a whole sheet so it wouldn't matter where the data ended up. I'll play around with it some more.
"Text to Columns" only works one column at a time, so you would have to do each column separately.

Perhaps another option may be to export the data in some sort of Text format. Then, instead of opening in Excel, you would import it into Excel, and you can set the format of the numbers at that time, so it is correct from the beginning, instead of trying to go back and fix it after the fact.
 
Upvote 0
I am currently working with the T2C feature and having some success. Inspired by your post, I realized I was making the T2C process harder than required, and it seems to be working. I am pursuing this as a solution, and thank you again for the input.

Louis
 
Upvote 0
You are welcome.
Let us know if you encounter any other issues.
 
Upvote 0
Thanks Sandy, I think I have a solution and will try your suggestion if I run into trouble.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,267
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top