# Number but leave Text

#### TiTuS

##### Board Regular
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

Timothy

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Hi,

Try:

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

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

It was supposed to be complete...

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

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!)

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.

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

Replies
2
Views
277
Replies
8
Views
250
Replies
5
Views
133
Replies
3
Views
276
Replies
0
Views
219

1,207,260
Messages
6,077,352
Members
446,279
Latest member
hoangquan2310

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back