extracting numberic values from text

[Robertsexton100]

Is there a formula to extract a numberic value from a cell containing both text and numbers?
I have a list of my retail stores which contain their "store number" and their "address" but not always in the same order and because they are all different addresses the length is different for each meaning I cannot use the =left or =right formula or text to columns.

I know this is probably a little basic but does anyone know a formula that will pick out the number only?

Many thanks
For any help
Cheers
Rob

 

[Gerald Higgins]

Could you give us a few different examples so that we can see what your data looks like ?

 

[pgc01]

Hi Rob

Your specification is very incomplete. Besides the data sample with the expected results that Gerald asked, please be thorough when defining the problem. How is the number: always an integer or sometimes with decimals? always only 1 number in each cell, or sometimes more than one? In this case what to do with the other ones? Are there also numbers in the address? Does the number you're after have a specific format, always the same number of digits? etc.

 

[Gunswick]

this UDF will do this, paste it into a VBA module:

Function MyNum(MyString) As Single
'gets numbers from a string e.g. tax (35.5%), returns 355; 34% returns 34
Dim i As Integer
For i = 1 To Len(MyString)
If IsNumeric(Mid(MyString, i, 1)) Then MyNum = MyNum & Mid(MyString, i, 1)
Next i
End Function

then in a blank cell enter:

=mynum(A1)

and just change your cell reference, it will not keep decimals though.

e.g. tax rate = 35.5%

will return 355

 

[Robertsexton100]

Sorry i wasn't more detailed, thank for the UDF Gunswick I'll definitly find a use for that in other problems I have.

The number is always 3 digits e.g 740,046,001,456...etc, and yes there is often other numbers in the address. However, I realised a few minutes ago that all the store numbers where preceeded by an "#", "¬" or "~" so I was able to use text to columns and splice out the number's I needed.
I tried to delete the thread but couldn't seem to delete it. Sorry for the inconvenience.

Thanks for the help though, i will defo use the UDF for something else.
Cheers
Rob