# Isolate numerical portion of cell containing text & numbers

#### Jaco Swart

##### New Member
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.

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### tygrrboi

##### Well-known Member
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

##### New Member
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

##### Well-known Member
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

##### Well-known Member
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.

Replies
5
Views
725
Replies
1
Views
392
Replies
4
Views
217
Replies
9
Views
576
Replies
7
Views
218

1,191,183
Messages
5,985,170
Members
439,944
Latest member
Vangelis74

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