Convert text to numbers

[Caravishah]

In the attached excel, I want to convert Col D (Total VAT) to numbers. Right now its all text and therefore i cannot perform any numerical functions. I have tried all the usual stuff like converting text to coloumns, value past by 1, VALUE function, etc. Nothing seems to work and therefore posting it here.

More than solution, I want to understand how to crack this.

many thanks
Ravi

 

[vletm]

Caravishah​

How did You get that data?
Are You sure that in could get 'better' way?
... for some reason, there is always something in front of Your texts which look like value.
Check cell F2 ... my Excel give 95.
Check cells G2 ... K2 ... those are better.
Check cell F3 ... that would be Your expected value from cell D3.

Your More than solution, I want to understand how to crack this.
If You'll have to do same 'cleaning' every time
... then Get Your data without those challenges?

 

[bosco_yip]

Or, try this formula way to remove the 1st character

In E2, formula copied down:

=0+SUBSTITUTE(D2,LEFT(D2),"")

Then, using Column E data for your calculation.

 

[p45cal]

You can do a find and replace:
Go into any offending cell in column D as if to edit it
Press the Home key on the keyboard
Hold the Shift key down while pressing the right-arrow key (cursor key) on the keyboard once. Nothing will appear to happen. Persevere. Let go of the Shift key.
Still on the key board press Ctrl+c (to copy the character to the clipboard)
Select all the offending cells in column D
On the keyboard, press Ctrl+h (brings up the search and replace dialogue box) and click in the Find what: field:
makes sure it's empty (press the delete and backspace keys a few times to ensure this) then Ctrl+v to paste the character. Again, nothing will be apparent.
Click in the Replace with: field and make sure it is completely empty. Other check boxes as below:


then click on Replace All button.
Dismiss any message about how many replacements made and Close the dialogue box.
Finished.

The leading character is unicode 8204, a non-joining zero-width character (hence you don't see it), possibly left over from a paste of values from a Chinese web site.

 

[Peter Bartholomew]

Another slight variant that will allow the first character to be trimmed without needing to identify it
= VALUE(REPLACE(Vat,1,1,""))
where Vat refers to the data column.