extracting 4 character numbers from text

  • Thread starter Thread starter JMay
  • Start date Start date
J

JMay

I've got a column of text data (100 rows), 25 to 30 characters in length.

Example cell g10: JMM/Receipts Clinic 4157 Morgantown
g11 MLC/Receits Forest Cln 4218

I need in cell H10 to enter a formula to extract only the "4157"
and in h11 athe same formula (relative) to extract only the "4218".
The placement of the 4XXX in Column G is not consistent.
Any ideas?

TIA,
 
Assuming the number always begins with 4 and is 4
characers long:

=MID(G10,FIND(4,G10),4)

HTH
Jason
Atlanta, GA
 
If that is the only numerical string in the whole text string this array
entered (ctrl + shift & enter)
will do it

=--MID(G10,MATCH(FALSE,ISERROR(--MID(G10,ROW(INDIRECT("1:100")),1)),0),100-S
UM(--ISERROR(1*MID(G10,ROW(INDIRECT("1:100")),1))))

(beware of line wrapping)

remove the first double minus if you want a text representation
 
I've got a column of text data (100 rows), 25 to 30 characters in length.

Example cell g10: JMM/Receipts Clinic 4157 Morgantown
g11 MLC/Receits Forest Cln 4218

I need in cell H10 to enter a formula to extract only the "4157"
and in h11 athe same formula (relative) to extract only the "4218".
The placement of the 4XXX in Column G is not consistent.
Any ideas?

TIA,
Click to expand...

The array-entered formula:

=MID(A1,MATCH(TRUE,(INT(MID(A1,
ROW(INDIRECT("1:"&LEN(A1))),4)/1000))>0,0),4)

will pick out the first four digit number in a string.

To *array-enter* a formula, hold down <ctrl><shift> while hitting <enter>. XL
will place braces {...} around the formula.

If you need to also check that the first digit is a four, then the
*array-entered* formula:

=MID(A1,MATCH(TRUE,(INT(MID(A1,
ROW(INDIRECT("1:"&LEN(A1))),4)/1000))=4,0),4)

will pick out the first four digit number in a string that starts with a 4.



--ron
 
Your suggestion was a great help, Tks

Ron Rosenfeld said:
The array-entered formula:

=MID(A1,MATCH(TRUE,(INT(MID(A1,
ROW(INDIRECT("1:"&LEN(A1))),4)/1000))>0,0),4)

will pick out the first four digit number in a string.

To *array-enter* a formula, hold down <ctrl><shift> while hitting <enter>. XL
will place braces {...} around the formula.

If you need to also check that the first digit is a four, then the
*array-entered* formula:

=MID(A1,MATCH(TRUE,(INT(MID(A1,
ROW(INDIRECT("1:"&LEN(A1))),4)/1000))=4,0),4)

will pick out the first four digit number in a string that starts with a 4.



--ron
Click to expand...
 
Back
Top