0

I have a list of prices in text with different currency formats in column A. I would like to create a formula that returns only the numbers from each cell in number. I tried substitute formula but I would have to substitute each symbol. 

$207 
193€ 
£178 
A$356 
$297CAD 
$1,602 
S$300
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
dataowl
  • 21
  • 1
  • 5

1 Answers1

0

=SUMPRODUCT(MID(0&A5, LARGE(INDEX(ISNUMBER(--MID(A5, ROW(INDIRECT("1:"&LEN(A5))), 1)) * ROW(INDIRECT("1:"&LEN(A5))), 0), ROW(INDIRECT("1:"&LEN(A5))))+1, 1) * 10

That should do the trick.

Credit: https://www.extendoffice.com/documents/excel/1622-excel-extract-number-from-string.html#a1

getHashSet
  • 41
  • 2