converting strings to numbers

65 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

>.
>

Reply all
Reply to author
Forward
0 new messages