Isolate numerical portion of cell containing text & numbers

[Jaco Swart]

Hi,

Is there is a formula that can look at a cell containing a lot of information and isolate just the numerical portion of the text. Unfortunately the text in all the cells is not consistent so I can’t just do a text to columns exercise.

For example if cell A1 may contain > contact: Piet email: piet@piet.com bank: XYZ Bank account number: 12345678 Address: 1 blue road, bellville, 7560.
I need to isolate just the account number (highlighted above) in example cell A2.

Hopefully there is a way to do this.

 

[tygrrboi]

if they always have the labels with a semicolon for the categories, you could try a mid function.

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'}span.s1 {color: #0057d6}span.s2 {color: #006107}</style>=MID(A1,FIND("email:", A1) + 7, FIND("bank:",A1)-FIND("email:", A1) -8)

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'}span.s1 {color: #0057d6}span.s2 {color: #006107}</style>
=MID(A1,FIND("Bank account number:", A1) + 21, FIND("Address:",A1)-FIND("Bank account number:", A1) -22)

So

MID(cell with data, FIND(category you're looking for, cell with data) + length of the category and space after colon, FIND(category after, cell with data) - FIND(category you're looking for, cell with data) - length of first category and TWO spaces, or one more than the previous number)

 

[Jaco Swart]

Thanx for your response but it does not seem to work because the text in the various lines is inconsistent, i.e.

- There isn’t a standard term used for bank account, it could be acc no., account, account number etc.
- The bank account numbers do not have a fixed length
- The account number may be in different parts of the text in each line, sometimes in the beginning of the text, middle or end etc.

 

[XOR LX]

Hi,

If the account numbers do not have a fixed length, and more than one number may appear within the string, what logic can we use to instruct Excel that the desired return for, for example, the string you give, i.e.:

contact: Piet email: piet@piet.com bank: XYZ Bank account number: 12345678 Address: 1 blue road, bellville, 7560

is the 8-digit number 12345678 and not the 4-digit number 7560?

Regards

 

[István Hirsch]

Thanx for your response but it does not seem to work because the text in the various lines is inconsistent, i.e.

- There isn’t a standard term used for bank account, it could be acc no., account, account number etc.
- The bank account numbers do not have a fixed length
- The account number may be in different parts of the text in each line, sometimes in the beginning of the text, middle or end etc.

Then you should list the set of rules which may help to recognize the account number (for example, it it always the longest among the numbers in the string?, etc.). If you do not provide any rules, excel can not recognize which number to extract.