Extracting numerals from an alphanumeric string

  • Thread starter Srinivasulu Bhattaram
  • Start date
S

Srinivasulu Bhattaram

I get a statement from my banker as a spread sheet

They look like this

INR18000.00 DR

INR19.37 CR



The numerals indicate the amount, INR indicates currency, Dr indicates Debit
and Cr indicates Credit

I need a formula which extracts the numerals in the string and put them in
another cell formatted for Currency

Can any one help?

Is it possible

seena
 
R

Rick Rothstein \(MVP - VB\)

Is the text in front of the number always a 3-character abbreviation
followed by a space? If so...

=MID(A1,4,LEN(A1)-6)

Rick
 
R

Rick Rothstein \(MVP - VB\)

And if not, use this array-entered** formula...

=MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),SUMPRODUCT(--ISNUMBER(--MID(A1,ROW($1:$999),1)))+1)

** Commit this formula with Ctrl+Shift+Enter, not Enter by itself.

Rick
 
R

Ron Rosenfeld

I get a statement from my banker as a spread sheet

They look like this

INR18000.00 DR

INR19.37 CR



The numerals indicate the amount, INR indicates currency, Dr indicates Debit
and Cr indicates Credit

I need a formula which extracts the numerals in the string and put them in
another cell formatted for Currency

Can any one help?

Is it possible

seena
Click to expand...

Entered normally (NOT as an array formula)

=LOOKUP(9.9E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"
0123456789")),ROW(INDIRECT("1:"&LEN(A1)))))
--ron
 
R

Ron Rosenfeld

I get a statement from my banker as a spread sheet

They look like this

INR18000.00 DR

INR19.37 CR



The numerals indicate the amount, INR indicates currency, Dr indicates Debit
and Cr indicates Credit

I need a formula which extracts the numerals in the string and put them in
another cell formatted for Currency

Can any one help?

Is it possible

seena
Click to expand...


I just split the formula in a different place, to make sure that inadvertent
word-wrap issues don't arise when you paste it in.

=LOOKUP(9.9E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},
A1&"0123456789")),ROW(INDIRECT("1:"&LEN(A1)))))
--ron
 
Top