Extracting scientific numbers from string VB Excel...

[kmothpur]

Hi Guys,
I am a learner of VB with Excel.....
I try to get the individual scientific number from the string which has spaces....
I have attached the code which I tried.....
Say a string " 9 1.25 9.26e05 ", I am expecting to isolate the numbers as 9, 1.25, 9.26E05.
Indeed the code does isolate the numbers, but it does as four numbers as 9, 1.25, 9.26, 0E00 instead of getting 3 numbers....
Any help gents.....
kmothpur

Dim TestChar As String
Dim Col As Integer
Dim Numbs As String
TestChar ="    9     1.25      9.26e05    "
From = 1
Howmany = 1
WSCol = 1
For From = 1 To Len(TestChar)
    C = Mid(TestChar, From, Howmany)
    C1 = Mid(TestChar, From + 1, Howmany)
    If IsNumeric(C) = True Or C = "." Or C = "e" Or C = "E" Then
        Numbs = Numbs & C
        If IsNumeric(C1) = False And C1 <> "." And C <> "e" And C <> "E" Then
            Worksheets("Sheet1").Cells(2, WSCol) = Numbs
            Numbs = 0
            WSCol = WSCol + 1
        End If
    End If
Next From

 

[NARAYANK991]

Hi ,

Try this :

Public Sub SeparateNumbers()
           Dim TestChar As String
           Dim WSCol As Integer, i As Integer
           Dim Numbs As Double
           Dim NotInNumber As Boolean
       
           TestChar = "    9    1.25      9.26e05    "
           If Right(TestChar, 1) <> " " Then TestChar = TestChar & " "
       
           WSCol = 1
           Do While Trim(TestChar) <> vbNullString
              NotInNumber = True
              i = 1
              Do While NotInNumber And i <= Len(TestChar)
                 If IsNumeric(Mid(TestChar, i, 1)) Then NotInNumber = False
                 i = i + 1
              Loop
           
              NextSpace = InStr(i - 1, TestChar, " ")
              Worksheets("Sheet1").Cells(2, WSCol).Value = Val(Mid(TestChar, i - 1, NextSpace - (i - 1)))
              WSCol = WSCol + 1
              TestChar = Mid(TestChar, NextSpace)
           Loop
End Sub

Narayan

 

[Smallman]

Hi

Bit slow on this one but here is my take.

Option Explicit

Sub SplitMe()
Dim ar() As String
Dim var As Variant
Dim str As String
Dim i As Integer
Dim j As Integer

    str = "    9    1.25      9.26e05    "
    ar = Split(str, " ")
    ReDim var(0, UBound(ar))
    For i = 0 To UBound(ar)
        If ar(i) <> "" Then var(0, j) = ar(i): j = j + 1
    Next i
    Range(Cells(1, 1), Cells(1, UBound(var, 2))) = var
End Sub

Take care

Smallman

 

[kmothpur]

Thanks Narayan for the reply.....

Indeed it works well for that string.....Excellent...

If there is a character, say 'a', present at the end of the string ex: " 9 1.25 9.26e05 a ", then the code splits into four numbers..... as
9, 1.25, 926000, 0.00E+00

Regards,

 

[kmothpur]

Thanks Smallman for the reply....

Works well for string contains spaces and numbers.....

If there is a character, say 'a', it splits as well. Sorry, I didn't mention in my question....

Regards,

 

[NARAYANK991]

Hi ,

Is there anything else you have not mentioned as yet ?

Try this :

Public Sub SeparateNumbers()
           Const NUMBERS = "0123456789,.E"
           Dim TestChar As String
           Dim WSCol As Integer, i As Integer, NextChar As Integer
           Dim Numbs As Double
           Dim InNumber As Boolean

   
           TestChar = "    9    1.25      9.26e05  a  "
           If Right(TestChar, 1) <> " " Then TestChar = TestChar & " "
           TestChar = UCase(TestChar)
       
           WSCol = 1
           Do While Trim(TestChar) <> vbNullString
              InNumber = False
              i = 1
              Do While Not (InNumber) And i <= Len(TestChar)
                 If InStr(1, NUMBERS, Mid(TestChar, i, 1)) > 0 Then InNumber = True
                 i = i + 1
              Loop
              j = i - 1
           
              Do While (InNumber) And i <= Len(TestChar)
                 If InStr(1, NUMBERS, Mid(TestChar, i, 1)) = 0 Then InNumber = False
                 i = i + 1
              Loop
           
              If i - 1 > j Then
                 Worksheets("Sheet1").Cells(2, WSCol).Value = Val(Mid(TestChar, j, i - j - 1))
                 WSCol = WSCol + 1
              End If
              TestChar = Mid(TestChar, i)
           Loop
End Sub

Narayan

 

[kmothpur]

Hi Narayan,
Thanks for that.... Brilliant...... It did work........
In the code, I have included '+-' in the Const NUMBERS = "0123456789,.E", so that to handle +/-in the scientific notation....
Thanks for your kind help.......
Regards,