Extracting integers from a text string.

[Bhupinder Rayat]

Hi all,

is it possilble to extract integers from a text string within a cell?

Eg.
Cell A1 - "ABC 123"
I want to extract the "123" from cell A1.

I have many text strings from which I need to extract the integers from.
The trouble is that each text string does not follow a consistent order, so I
cannot use text functions such as LEFT, MID or RIGHT to extract what i need.

Any help would be much appreciated.


Regards,


Bhupinder Rayat

 

[Roger Govier]

Hi

DO they all have a space within them?
Do you always want whatever comes after the space?
If so
=MID(FIND(" ",A1)+1,255)

Regards

Roger Govier

 

[Roger Govier]

Correction, forgot the original cell reference
=MID(A1,FIND(" ",A1)+1,255)

Regards

Roger Govier

 

[Bhupinder Rayat]

Hi,

They do have spaces within them, but it is not as simple as that. integers
can be anywhere within the text string, even at the start of it. Your formula
does not give me what I what. Thanks for the try, any other offers?

Regards,

Bhupinder

 

[RagDyeR]

If you have *one* set of numbers in the string, with *no* spaces between the
numbers, try this *array* formula.

=--MID(A1,MATCH(FALSE,ISERROR(--MID(A1,ROW(INDIRECT("1:100")),1)),0),100-SUM
(--ISERROR(1*MID(A1,ROW(INDIRECT("1:100")),1))))

Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

message Hi,

They do have spaces within them, but it is not as simple as that. integers
can be anywhere within the text string, even at the start of it. Your
formula
does not give me what I what. Thanks for the try, any other offers?

Regards,

Bhupinder

 

[Bhupinder Rayat]

RayDyer,

I can see wat are trying to achieve, it may work, but excel does not like
the formula.
You have inserted "--" within the formula, what is this for?

Regards,

Bhupinder

 

[David Billigmeier]

RagDyeR's formula worked for me. Remember you have to enter the formula by
pressing CTRL+SHIFT+ENTER... **NOT** just Enter. The "--" is for changing
boolean TRUE/FALSE valuse to 1/0 so multiplication/addition can take place.

 

[Bhupinder Rayat]

Hi,

Yea the formula works like a charm. I have used array formulas before, it
didnt accept the formula because the 2nd line of the formula got pasted into
a the new line. Its been a long day!!

thanks guys.


Bhupinder.

 

[RagDyeR]

What do you mean by "excel does not like the formula"?

The formula works in the 3 versions of XL that I have access to; 97 - 2000 -
2002.

And, among other things, the double unary (--) converts numeric text to
true numbers.

In A1 enter:
ABC123

In B1 enter:
=RIGHT(A1,3)

In B2 enter:
456

In B3 enter:
=SUM(B1:B2)

Note the return in B3.

NOW, change the formula in B1 to:
=--RIGHT(A1,3)

And see the change in the return of B3!
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------



message RayDyer,

I can see wat are trying to achieve, it may work, but excel does not like
the formula.
You have inserted "--" within the formula, what is this for?

Regards,

Bhupinder

 

[B. R.Ramachandran]

Hi,

The following formula, even though somewhat clumsy, should work. It should
accept the following string formats, ABC 890, 123 XYZ, and ABC 456 XYZ, vis a
vis any length(s) of alpha- and numeric- subsections in each string.

=IF(ISNUMBER(LEFT(A1,1)*1),LEFT(A1,FIND("
",A1)-1),IF(ISNUMBER(RIGHT(A1,1)*1),RIGHT(A1,LEN(A1)-FIND("
",A1)),LEFT(RIGHT(A1,LEN(A1)-FIND(" ",A1)),FIND(" ",RIGHT(A1,LEN(A1)-FIND("
",A1)))-1)))

Maybe, there is a much simpler and elegant approach, or a built-in function
I am not aware of, to your problem!

Regards,
B. R. Ramachandran

 

[Gord Dibben]

Bhupinder

OK with a macro?

Sub RemoveAlphas()
'' Remove alpha characters from a string.
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String

Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)

For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Mid(rngR.Value, intI, 1) Like "[0-9.]" Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR
End Sub

Note: this will alter the original data with no undo. May be best to try it
on a copy of your worksheet if you want to preserve original data.


Gord Dibben Excel MVP