Strip out text from cells

[Celticshadow]

Hi All

Back again doh. May I ask the board if they would be kind enough to help me get a formula to strip out the text from the attached. I hope that the instuction contained within is clear enough and I thank you in advance.

Kind Regards

https://spreadsheets.google.com/spr...ffBUodGdJZFl5TWhEOURpTmlDU3ptOUxwZ2c&hl=en_US

 

[hiker95]

Celticshadow,

Your link only shows a graphic of a part of your worksheet.

Extract numbers from alpha/numerical string.

If your string is in cell A1, then put this in B1:
=MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),99)

 

[Biz]

Why not use UDF

Hi

The UDF function can extract Numbers or Alphabets
In Your case numbers then use
=AlphaNum(A1,FALSE)

Function AlphaNum(txt As String, Optional Alpha As Boolean = True) As String
'Use in cell like _
=AlphaNum(A1,True) '<- True for Alphabets, False for Numbers
With CreateObject("VBScript.RegExp")
    .Pattern = IIf(Alpha, "\d+", "\D+")
    .Global = True
    AlphaNum = .Replace(txt, "")
End With
End Function

</PRE>
Biz
<!-- / message --><!-- edit note -->

 

[Aladin Akyurek]

Hi All

Back again doh. May I ask the board if they would be kind enough to help me get a formula to strip out the text from the attached. I hope that the instuction contained within is clear enough and I thank you in advance.

Kind Regards

https://spreadsheets.google.com/spr...ffBUodGdJZFl5TWhEOURpTmlDU3ptOUxwZ2c&hl=en_US

B2, just enter and copy down:

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

 

[T. Valko]

B2, just enter and copy down:

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

Looks like a mistake with those repeated +307.

Try this...

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

 

[Aladin Akyurek]

B2, just enter and copy down:

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

Typo...

9.99999999999999E+307+307 should be:

9.99999999999999E+307

 

[Sektor]

UDF alternative.
To Biz. Since OP wants first occurrence of number, we don't need Global = True.

[COLOR="Blue"]Function[/COLOR] Extr(Str [COLOR="Blue"]As[/COLOR] String) [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]
    [COLOR="Blue"]With[/COLOR] CreateObject("VBScript.RegExp")
        .Pattern = "\d+"
        Extr = .Execute(Str)(0)
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Function[/COLOR]

 

[Celticshadow]

Hi Guys

To the chaps whom gave some coding thanks again but I do not know how to use coding. To the other guys whom gave me the formula thanks again also, it partially works but having tried it on some new data today there seems to be a problem (more my fault). As in on of the cells I tried it on there is in cell B2 the data 12 p which the formula gives the result of 0.5(see below in text)? May I also ask is there an easy way to attach an image on here instead of using google docs?

Kind Regards

Col a2
Data
12 p

Col b2
Result
0.5 (should be 12)

 

[hiker95]

Celticshadow,

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

 

[T. Valko]

Hi Guys

To the chaps whom gave some coding thanks again but I do not know how to use coding. To the other guys whom gave me the formula thanks again also, it partially works but having tried it on some new data today there seems to be a problem (more my fault). As in on of the cells I tried it on there is in cell B2 the data 12 p which the formula gives the result of 0.5(see below in text)? May I also ask is there an easy way to attach an image on here instead of using google docs?

Kind Regards

Col a2
Data
12 p

Col b2
Result
0.5 (should be 12)

Ah ha! It's the old "evaluate everything as a time" quirk!

Excel is eager to evaluate dates and times. It sees 12 p as a "shorthand" version of 12:00 PM.

In Excel times are really just numbers formatted to look like times just so we as humans will be able to recognize the entry as a time entry.

Time is the fractional part of a day. A day has the numeric value of 1. 12:00 PM is mid point of the day (half of the day) so the mid point of 1 is 0.5. That's how Excel is evaluating the string "12 p". "12 a" would evaluate to 0.

So, this makes the formula bit more complicated.

=LOOKUP(1E100,--MID(SUBSTITUTE(SUBSTITUTE(UPPER(A2),"P","x"),"A","x"),MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),ROW(INDIRECT("1:"&LEN(A2)))))