Reputation:

Cannot convert a cell with text to number with excel

I have a table in excel in which there is a column with text which contains currency and some trailing spaces.

I have something like

"
Extra Charges  37,95 €  
    + 1,50 € Shipping
    + 2,50 € COD
41,95 €  "

So I do

=TRIM(RIGHT(TRIM(Table[@PRICES]);9))

to get the 41,95 € and

=VALUE(IF(LEN(Table[@PRICES])>9;MID(Table[@PRICES];20;8);Table[@PRICES]))

to get the 37,95 €

But for the first (41,95 €) if I try to add value(..) function I get #VALUE error

How can I convert it to number?

I tried to copy to do the trimming and value conversion in another cell and just copy the value inside the table but same result
NUMBERVALUE() is not working either I get #NAME? error

Upvotes: 0

Answers (2)

user10358702

Reputation:

I tried the following inserting a CLEAN and it worked

=TRIM(RIGHT(TRIM(CLEAN(Table[@PRICES]));9))

Upvotes: 0

Spainey

Reputation: 402

You can use the TEXT() function to convert a numeric string to a number:

=TEXT(REPLACE(LEFT("41,95 €",FIND(" ","41,95 €")),FIND(",","41,95 €"),1,"."),"0.00")

The FIND() function returns the position of the search string within an existing string. This is helpful for dynamically detecting the end point for use in the LEFT() function. Additionally, it is used to find the position of the "," character in the number so that we can replace it with a "." - for excel to recognise it as a number. The TEXT() function accepts the format "0.00" for a number with two decimal places.

Unfortunately, this method drops the "Euro" symbol - so you may want to use cell formatting for your own reference.

Upvotes: 2

Cannot convert a cell with text to number with excel

Answers (2)

Related Questions