CODEWITHSUNDEEP
excel-formulaexcel-2010
Jack Vuong
Jack Vuong

Reputation: 19

excel separate text and numbers

This is an excel problem. I need to separate text and numbers in a string. The string can start with numbers or can start with characters. There could be spaces in between text or numbers. So the formula needs to be versatile enough to separate it into 2 columns with one column consisting of only text and the other only numbers. Please help.

Thank you very much

Examples of text strings

Upvotes: 1

Views: 8037

Answers (2)

Ashutosh
Ashutosh

Reputation: 11

Only Text

Function Alphas(ByVal strInString As String) As String Dim lngLen As Long, strOut As String Dim i As Long, strTmp As String

lngLen = Len(strInString)
strOut = ""
For i = 1 To lngLen
    strTmp = Left$(strInString, 1)
    strInString = Right$(strInString, lngLen - i)
    'The next statement will extract BOTH Lower and Upper case chars
    If (Asc(strTmp) >= 65 And Asc(strTmp) <= 90 Or Asc(strTmp) >= 97 And Asc(strTmp) <= 122) Then
        'to extract just lower case, use the limit 97 - 122
        'to extract just upper case, use the limit 65 - 90
        strOut = strOut & strTmp
    End If
Next i
Alphas = strOut

End Function

Only Numbers

Function Numerics(ByVal strInString As String) As String Dim lngLen As Long, strOut As String Dim i As Long, strTmp As String

lngLen = Len(strInString)
strOut = ""
For i = 1 To lngLen
    strTmp = Left$(strInString, 1)
    strInString = Right$(strInString, lngLen - i)
    If (Asc(strTmp) >= 48 And Asc(strTmp) <= 57) Then
        strOut = strOut & strTmp
    End If
Next i
Numerics = strOut

End Function

Only Number & Text

Function Alphanumerics(ByVal strInString As String) As String Dim lngLen As Long, strOut As String Dim i As Long, strTmp As String

lngLen = Len(strInString)
strOut = ""
For i = 1 To lngLen
    strTmp = Left$(strInString, 1)
    strInString = Right$(strInString, lngLen - i)
    'The next statement will extract BOTH Lower and Upper case chars
    If (Asc(strTmp) >= 65 And Asc(strTmp) <= 90 Or Asc(strTmp) >= 97 And Asc(strTmp) <= 122 or Asc(strTmp) >= 48 And Asc(strTmp) <= 57) Then
        'to extract just lower case, use the limit 97 - 122
        'to extract just upper case, use the limit 65 - 90
        strOut = strOut & strTmp
    End If
Next i
Alphanumerics = strOut

End Function

This can use in excel too but this is modified by me for Access use

Upvotes: 1

user667489
user667489

Reputation: 9569

As long as all your cells in column A are of the form {numbers + spaces}{non-numeric text} or the reverse, you can start by finding the positions of the first numeric character and the first non-numeric, non-space character in each cell. You could then use those plus a bit of extra logic to extract the appropriate substrings using MID.

I posted array formulas to do this here: Excel formula to find the first non-alpha character in a cell?

You would need to make a slight modification to the second formula, so it returns the position of the first character that isn't a number or a space:

=MIN(
        IF(
                  1*ISNUMBER(
                  1*MID(
                      A1,
                      ROW(INDIRECT("A1:A"&LEN(A1))),
                      1
                  )
                ) +
                1*(MID(
                      A1,
                      ROW(INDIRECT("A1:A"&LEN(A1))),
                      1
                  )=" "),
            LEN(A1)+1,
            ROW(INDIRECT("A1:A"&LEN(A1)))

        )
    )

Upvotes: 0

Related Questions