Lookup numbers only

[MeAgain]

Hi again
I know to different kind on cell containing data like in
cellA1 614L (I want in in cellA2 only the number 614)
cellB1 K314 (I want in cellB2 only the number 314)
Could you please give a formula for this thanks.

 

[MeAgain]

Sorry about the mistake in the first mail.

I have different kind of cell containing data like in
cellA1 614L (I want in in cellA2 only the number 614)
cellB1 K314 (I want in cellB2 only the number 314)
Could you please give a formula for this thanks.

 

[Alan Beban]

In A2: =LEFT(A1,LEN(A1)-1)
In B2: =RIGHT(B1,LEN(B1)-1)

Alan Beban

 

[MeAgain]

thanks it works but for only right and left.
What would be the (universal) formula for in both condition. ie either the
cell contain K313 or 614L I want the result 313 and 614.
thanks

 

[Dave Peterson]

I think a formula to cover all the possibilities would be pretty complex.
Either you could give more specifics (like always 4 characters with a single
letter either at the beginning or end, but never embedded) and it would make it
easier to write.

But if your text had a bunch of different possible formats 614L 61L4 6L14 l614
or even 123qeqr4562wew and you wanted to extract just the digits (0-9, no
decimal points, no negative signs), then you could use a user defined function
like this:

Option Explicit
Function JustNumbers(rng As Range) As Variant
Dim iCtr As Long
Dim myNumbers As String

Set rng = rng(1)
myNumbers = ""
For iCtr = 1 To Len(rng.Value)
If Mid(rng.Value, iCtr, 1) Like "#" Then
myNumbers = myNumbers & Mid(rng.Value, iCtr, 1)
End If
Next iCtr

JustNumbers = myNumbers

End Function

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Type in some test data into an empty cell (A1?) and then put this in another
cell:

=justNumbers(a1)

And see if it worked.

 

[Alan Beban]

Does Dave Peterson's solution in this thread not work?

Alan Beban

 

[MeAgain]

excellent
thanks

I think a formula to cover all the possibilities would be pretty complex.
Either you could give more specifics (like always 4 characters with a single
letter either at the beginning or end, but never embedded) and it would make it
easier to write.

But if your text had a bunch of different possible formats 614L 61L4 6L14 l614
or even 123qeqr4562wew and you wanted to extract just the digits (0-9, no
decimal points, no negative signs), then you could use a user defined function
like this:

Option Explicit
Function JustNumbers(rng As Range) As Variant
Dim iCtr As Long
Dim myNumbers As String

Set rng = rng(1)
myNumbers = ""
For iCtr = 1 To Len(rng.Value)
If Mid(rng.Value, iCtr, 1) Like "#" Then
myNumbers = myNumbers & Mid(rng.Value, iCtr, 1)
End If
Next iCtr

JustNumbers = myNumbers

End Function

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Type in some test data into an empty cell (A1?) and then put this in another
cell:

=justNumbers(a1)

And see if it worked.

 

[Dale Hymel]

if the cell you are attempting to extract the numbers from is always 4
characters long with an alpha code in the first or fourth position, then the
following formula would work fine.
=IF(CODE(A1)<60,LEFT(A1,3),RIGHT(A1,3))

code() returns the ASCII code for the first character in the string. A
return of 60 or less means it is a number. 65 and above a letter.

This formula returns a text string. If you want values then
=VALUE(IF(CODE(A1)<60,LEFT(A1,3),RIGHT(A1,3)))

 

[Nutter]

thanks for your time.

if the cell you are attempting to extract the numbers from is always 4
characters long with an alpha code in the first or fourth position, then the
following formula would work fine.
=IF(CODE(A1)<60,LEFT(A1,3),RIGHT(A1,3))

code() returns the ASCII code for the first character in the string. A
return of 60 or less means it is a number. 65 and above a letter.

This formula returns a text string. If you want values then
=VALUE(IF(CODE(A1)<60,LEFT(A1,3),RIGHT(A1,3)))