TEXT TO NUMBER FORMAT

JLouis

Board Regular
Joined
Jan 1, 2004
Messages
165
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.
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,939
Office Version
  1. 365
Platform
  1. Windows
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.
 

JLouis

Board Regular
Joined
Jan 1, 2004
Messages
165
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.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,939
Office Version
  1. 365
Platform
  1. Windows
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.
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499

ADVERTISEMENT

did you try Clear Formats on whole range?
 

JLouis

Board Regular
Joined
Jan 1, 2004
Messages
165
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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,939
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You are welcome.
Let us know if you encounter any other issues.
 

JLouis

Board Regular
Joined
Jan 1, 2004
Messages
165
Thanks Sandy, I think I have a solution and will try your suggestion if I run into trouble.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,755
Messages
5,597,929
Members
414,193
Latest member
bb60

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
Top