Converting text into a number

[stephenrowell]

I am new to Mr.Excel and thank all of you who post answers.

I import data from an internet based source into a column of a worksheet. The data has the following format and comes in as text.
155,25$
1.094,85$
28,35$
0,00$
There are also empty cells in the column.

I wish to convert these text entries into numbers in the next column to the right. Please advise on a way to acomplish this. Is there a way that I can convert all the numbers in the entire column?

Thanks,
Stephen Rowell

 

[Scott Huish]

Hi, welcome to the board!!

=IF(A1="","",SUBSTITUTE(A1,"$","")+0)

Copy down.

 

[stephenrowell]

Thanks for the quick response. I tried the formula and got the result of text without the $ at the end. The text was not converted to a number which I could use as a value in a formula. I may have not entered the formula in the correct fashion. The IF portion of the formula results in a False outcome and the Substitution is then entered in the false condition. Is that correct?

 

[Rick Rothstein]

Thanks for the quick response. I tried the formula and got the result of text without the $ at the end. The text was not converted to a number which I could use as a value in a formula.

Is the decimal point for your system the dot or the comma?

 

[stephenrowell]

Thanks for your reply.

The text that comes in from the import uses a dot for seperating numbers above 999 and a comma for the 2 place decimal. I hope this answers your question. I need to convert the text into a proper number value. Formatting I can do after the conversion to a value.

 

[Rick Rothstein]

The text that comes in from the import uses a dot for seperating numbers above 999 and a comma for the 2 place decimal. I hope this answers your question. I need to convert the text into a proper number value. Formatting I can do after the conversion to a value.

I can see what the decimal point is for the text you posted... my question dealt with your system... your computer... what is the decimal point on it (that is, when you type a floating point number into Excel... what do you type for the decimal point?

 

[stephenrowell]

Sorry for my confusion. I appologize. I type a dot as the decimal point seperator in my computer.

 

[Rick Rothstein]

I type a dot as the decimal point seperator in my computer.

Give this formula a try...

=1*SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"$",""),".",""),",",".")

 

[Scott Huish]

Post Removed

 

[stephenrowell]

Rick,

Thanks so much. That worked like a charm. I appreciate your patients with my confusion. Have a great rest of the day.