Converting Text to Values

[Guest]

Hello,

I get a large block of data from another source that is in text format that
I need converted to values in order to perform calculations. Beyond creating
a second worksheet linked to the first, I'm at a loss (e.g.
=Value(Sheet1!A1)). Any suggestions using VBA?

TIA!

 

[Arvi Laanemets]

Hi

1st way:
Format the block of data as general or number;
Insert a number 1 (or 0) into any free cell, and copy it;
Select your data;
Paste Special - check Multiply (or Add), and click OK.

2nd way:
Select a range from column;
Format the range as general or number;
From Data menu, select TextToColumns, continue with delimited, remove all
delimiters (in case you have not only numbers in selected range), set column
format to General, and click Finish. (When you have only numbers in text
format - without any spaces etc. - then simply TextToColumns>Finish).

 

[Guest]

Embarrassingly simple.... Thank you.

Hi

1st way:
Format the block of data as general or number;
Insert a number 1 (or 0) into any free cell, and copy it;
Select your data;
Paste Special - check Multiply (or Add), and click OK.

2nd way:
Select a range from column;
Format the range as general or number;
From Data menu, select TextToColumns, continue with delimited, remove all
delimiters (in case you have not only numbers in selected range), set column
format to General, and click Finish. (When you have only numbers in text
format - without any spaces etc. - then simply TextToColumns>Finish).