Number but leave Text

[TiTuS]

Hi,
Just working on a spreedsheet and have got a bit stuck.
i am using this code below that i was told a very long time ago by Paddy i think, but have just tryed to use it for a different use and having problems.
Heres the code

Function EXTRACTNUMBER(TARGET As Range, Optional NUM As Boolean = True) As Variant
Dim n, TmpVal

If TARGET.Cells.Count > 1 Or Len(TARGET) < 1 Then
EXTRACTNUMBER = CVErr(1)
Exit Function
End If

For n = 1 To Len(TARGET)
If NUM = True Then
Select Case Mid(TARGET, n, 1)
Case 0 To 9: EXTRACTNUMBER = EXTRACTNUMBER & Mid(TARGET, n, 1)
End Select
Else
Select Case Mid(TARGET, n, 1)
Case 0 To 9:
Case Else: EXTRACTNUMBER = EXTRACTNUMBER & Mid(TARGET, n, 1)
End Select
End If
Next n

End Function


The problem i am having is as follows:

I have a list of data that is in currency form Eg: $US320.00

I want to extract the number but keep it as currency, what i am finding is that this code turns it into something like this: 32000 instead of 320.00

Is any one able to enlighten me on how to extract the $US part but have it stay in currency form to be added with many other curencys?

i tryed using another formula i found in excel help where it takes away as many digits as you want either from the right or the left using the LEN function but for some reason after using that if i added multiple entries that had been sifted with the formula the answer was always 0

In advance Thankyou for your time!

Timothy

 

[fairwinds]

Hi,

Try:

=--SUBSTITUTE(A1,"$US","")

 

[TiTuS]

Getting #VALUE error off that, do i need to put other data into the formula or is that a complete formula?

ahh it was cause of the "--'s) in the start of your formula but even then it just gives me the same as what was in A1

A1 = $US320.00

if i enter that formula into say B1 it just gives me $US320.00

 

[fairwinds]

It was supposed to be complete...


How about:

=--SUBSTITUTE(TRIM(A1),"$US","")

 

[TiTuS]

Ok that works a treat at getting rid of the $US but im still having the same problem with adding them.. if i have done say 3 of them in a row then sum the numbers that were given off with formula =SUM(A1:A3) it gives the answer of 0


EDIT: it seems to work fine if i add each answer indervidually eg: =A1+A2+A3 but its so much easier if i can get it to work with the =sum(A1:A3) since i do have loads of data to add like this...

(BTW thankyou so much for your fast response and great help so far!)

 

[fairwinds]

Try to put the --'s back in the beginning of the formula, that turns them into true numbers that can be summed by a SUM formula.

 

[TiTuS]

Ahh your a legand! Again thanks heaps (dont know why i had problems with the --'s to start with but now that ive put them back in its perfect!!!
Thankyou so so much this is gonna save loads of work!

Timothy