Extracting numerals from an alphanumeric string

  • Thread starter Thread starter Srinivasulu Bhattaram
  • Start date 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
 
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
 
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
 
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
 
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
 
Back
Top