Extracting numbers from text in excel

[bjnockle]

Looking for formula to extract numbers only from text values in excel. Non-array formula preferred.

Sample file attached.

[AlKey]

Enter in B2 and copy down

Please Login or Register  to view this content.

v	A	B
1	Text	Number
2	INR:1	1
3	INR:2	2
4	INR:3	3
5	INR:4	4
6	INR:5	5
7	INR:6	6
8	INR:7	7
9	INR:8	8
10	INR:9	9
11	INR:10	10
12	INR:11	11

[kasan]

Hi,In case all your text is in the same format - this formula in B2 should help:

Please Login or Register  to view this content.

[Tony Valko]

Here's another one...

Data Range

	A	B
1	Text	Number
2	INR:1	1
3	INR:2	2
4	INR:3	3
5	INR:4	4
6	INR:5	5
7	INR:6	6
8	INR:7	7
9	INR:8	8
10	INR:9	9
11	INR:10	10

This formula entered in B2 and copied down:

=--MID(A2,5,10)

[Phuocam]

Or try this ...

=--REPLACE(A2,1,FIND(":",A2),"")

[Tony Valko]

Or try this ...

=--TRIM(RIGHT(SUBSTITUTE(A1,":",REPT(" ",50)),40))

[AlKey]

Or try this ...

=--TRIM(RIGHT(SUBSTITUTE(A1,":",REPT(" ",50)),40))

When converting text to numeric values you don't need to use TRIM function. The double unary operator (--) will remove spaces anyway.

=--RIGHT(SUBSTITUTE(A2,":",REPT(" ",50)),40)

[Tony Valko]

Or try this...

=LOOKUP(1E100,--RIGHT(A1,ROW(INDIRECT("1:"&LEN(A1)))))

[Tony Valko]

Or try this ...

=LOOKUP(9.99999999999999E+307,--RIGHT(A1,ROW(INDIRECT("1:"&LEN(A1)))))

[AlKey]

Or this

Please Login or Register  to view this content.

[Tony Valko]

Or try this ...

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

[Pete_UK]

Another approach, that doesn't use a formula:

Copy cells A2:A123 across into column B. With the cells still highlighted, do CTRL-H (Find & Replace), then:

Find What: INR:
Replace with: leave blank
Click Replace all

You might want to change the formatting (horizontal alignment) to General.

Hope this helps.

Pete

[dmg2016]

Anther non-formula method:

Data→Text to columns→Delimiter Other→:
or
Data→Text to columns→Fixed→Move the slider in between : and the number

DMG