Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

converting strings to numbers

77 views
Skip to first unread message

Peter Strobl

unread,
Sep 26, 2001, 10:03:19 AM9/26/01
to
is there a way to extract a number from a text string?
e.g. "200 Std." should be returned as a number with the
value 200

Alan Perkins

unread,
Sep 26, 2001, 10:41:00 AM9/26/01
to
Given your example,
=VALUE(LEFT(A1,3))
will do the trick.

However, if the length of the numeric part varies, you could use

=VALUE(LEFT(A1,SEARCH(" ",A1)-1))
which will find the first space in the text, and use that to calculate the
number of characters in the number.

HTH

Alan P.


"Peter Strobl" <Peter....@dlr.de> wrote in message
news:111501c14693$feb11700$b1e62ecf@tkmsftngxa04...

David McRitchie

unread,
Sep 26, 2001, 1:05:01 PM9/26/01
to
Hi Peter,
Your other answer thus far extracted only the left 3 positions
which is very specific, and you did not say leftmost 3 characters
is the number.

a26 26 =extractdigits(A23)
26abc3a 26 =extractdigits(A24)
abc3a 3 =extractdigits(A25)
1a2a3a 1 =extractdigits(A26)
abc returns a null string

Function ExtractDigits(cell As String) As Variant
'extract 1st continuous set of digits
'David McRitchie, 2001-09-26
Dim i As Long, flag As Long
flag = 0
ExtractDigits = ""
For i = 1 To Len(cell)
If Mid(cell, i, 1) >= "0" And _
Mid(cell, i, 1) <= "9" Then
flag = 1
ExtractDigits = ExtractDigits & Mid(cell, i, 1)
ExtractDigits = ExtractDigits * 1
Else
If flag = 1 Then Exit Function
End If
Next i
End Function


Somewhat related:
Here is a User Defined Function that extracts the number and
allows you to add a number to it, posted by Myrna Larson
http://groups.google.com/groups?as_umsgid=o8qs4tgdosil425t5...@4ax.com
i.e. AB12 to AB13


I think someone posted an array formula to extract any number that
appears on the left, but I don't have a reference to it.

HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Macros: http://www.geocities.com/davemcritchie/excel/excel.htm
Search Page: http://www.geocities.com/davemcritchie/excel/search.htm

"Peter Strobl" <Peter....@dlr.de> wrote in message news:111501c14693$feb11700$b1e62ecf@tkmsftngxa04...

George Simms

unread,
Sep 26, 2001, 1:49:14 PM9/26/01
to
Hi Peter,

This *array formula will extract numbers to the right from a text string in
cell A1 .

=MID(A1,1,MATCH(TRUE,ISERROR(1*MID(A1,ROW(INDIRECT("1:25")),1)),0)-1)*1

*Array formula Must be entered by holding down the Ctrl & Shift keys then
hit Enter. Do this after you select or edit the formula. *Excel* will
enclose the formula in { } if it's entered correctly.

All The Best

George Simms
Microsoft MVP - Excel

Newcastle upon Tyne
England

"Peter Strobl" wrote in message ...

Peo Sjoblom

unread,
Sep 26, 2001, 6:30:06 PM9/26/01
to
Hi Peter,

this array entered formula (Ctrl+Shift&Enter) extracts the
number to the left, right, or in the middle of the text

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

Regards,

Peo Sjoblom

>is there a way to extract a number from a text string?
>e.g. "200 Std." should be returned as a number with the
>value 200

>.
>

0 new messages