Split Text and number in a excel cell using vba

Question

I have a cell text "12-3 0000 9 FLY AIR Make MY Trip" and I want the output like 12-3 0000 9 into next cell and the next sequence cell "FLY AIR Make MY Trip".

Public Function SplitText(pWorkRng As Range, pIsNumber As Boolean) As String
    'Updateby20150306
    Dim xLen As Long
    Dim xStr As String

    xLen = VBA.Len(pWorkRng.Value)

    For i = 1 To xLen
        xStr = VBA.Mid(pWorkRng.Value, i, 1)
        If ((VBA.IsNumeric(xStr) And pIsNumber) Or (Not (VBA.IsNumeric(xStr)) And Not (pIsNumber))) Then
            SplitText = SplitText + xStr
        End If
    Next
End Function

=SplitText(A2,FALSE) into a blank cell to get the only the text.

Result as 12300009

=SplitText(A2,TRUE) into a blank cell to get the only the number.

Result as "- FLY AIR Make MY Trip"

You have posted insufficient test data to reach conclusive solution. — shrivallabha.redij
– shrivallabha.redij, Commented Nov 7, 2017 at 7:01
"12-3 0000 9 FLY AIR Make MY Trip" i wanna to split into "12-3 0000 9" and "FLY AIR Make MY Trip" — Vinoth Narayan
– Vinoth Narayan, Commented Nov 7, 2017 at 7:14

T.M. · Accepted Answer · 2017-11-07 08:45:00Z

1

Assume you want to get the numeric first part, if your parameter pIsNumberis set true:

Option Explicit
Public Function SplitText(pWorkRng As Range, pIsNumber As Boolean) As String
'Declare ALL variables
Dim xLen As Long
Dim xStr As String
Dim i As Integer
xLen = VBA.Len(pWorkRng.value)
For i = 1 To xLen
    xStr = VBA.Mid(pWorkRng.value, i, 1)
     If pIsNumber And InStr(" -0123456789", xStr) Then
        SplitText = SplitText & xStr   ' use ampersand "&" instead of "+"

     ElseIf Not pIsNumber And InStr("-123456789", xStr) = 0 Then
        SplitText = SplitText & xStr
     End If
Next
If pIsNumber Then SplitText = Replace(SplitText, String(3, " "), " ")
End Function

Note

Use ampersand "&" instead of "+" to join string values. BTW, don't forget to declare your variables and always use Option Explicit in the declaration head of your code module.

Good luck.

answered Nov 7, 2017 at 8:45

T.M.

10k3 gold badges40 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Vinoth Narayan Over a year ago

Thanks a lot it works.but for text its coming along with ""000 FLY AIR Make MY Trip".for number it works perfectly

T.M. Over a year ago

I tested above code again and it works for me. Suppose you omitted the blank space character " " in the comparation string of the first condition: If pIsNumber AND Instr(" -0123456789", xStr) Then, whereas the comparation string in the Else condition starts without blank.

Jochen · Accepted Answer · 2017-11-07 09:14:27Z

1

I would use Split, check for text and then join again:

Function SplitText(Txt As String, Optional Number As Boolean = True)
Dim Arr, outarr() As String, I As Integer
Dim nbr As String, rest As String
Arr = Split(Txt, " ")
ReDim outarr(UBound(Arr))
    For I = LBound(Arr) To UBound(Arr)
        outarr(I) = Arr(I)
        If Not IsNumeric(Left(Arr(I), 1)) Then
            ReDim Preserve outarr(I - 1)
            nbr = Join(outarr, " ")
            rest = Mid(Txt, Len(nbr) + 2)
            Exit For
        End If
    Next I
    SplitText = IIf(Number, nbr, rest)
End Function

answered Nov 7, 2017 at 9:14

Jochen

1,2541 gold badge7 silver badges9 bronze badges

Comments

shrivallabha.redij · Accepted Answer · 2017-11-07 09:28:40Z

1

Assuming your text is in cell A2

Enter following formula where you need numeric portion (to be entered as array formula i.e. CTRL+SHIFT+ENTER and not just ENTER):

=LEFT(A2,MIN(IFERROR(SEARCH(CHAR(ROW($A$65:$A$90)),A2,1),2^15))-1)

If applied correctly Excel will show surrounding braces {}.

Then assuming you have above formula in cell B2, its result can be used as output to get the remaining text part like below:

=SUBSTITUTE(A2,B2,"")

If it works then you will not require VBA.

answered Nov 7, 2017 at 9:28

shrivallabha.redij

5,9021 gold badge15 silver badges28 bronze badges

2 Comments

Vinoth Narayan Over a year ago

It returns me empty

shrivallabha.redij Over a year ago

@VinothNarayan I am not sure how you are using it. See attached file for implementation. Example File

Collectives™ on Stack Overflow

Split Text and number in a excel cell using vba

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related