split word and number into col

[smallxyz]

I have data as shown belows and wanna split them into columns, one for words, another for numbers.

Excel 2010
	A	B
1	aaa aaaa aaaaaaaaa 123
2	aaaaaaa aaaa 12345
3	aaa aa 1234567
4	aaaaaaaaaa aaaaaa 123
5	aaa aaaa aaa aaaaaaa aaa a aa 12
6
7
8
9	aaa aaaa aaaaaaaaa	123
10	aaaaaaa aaaa	12345
11	aaa aa	1234567
12	aaaaaaaaaa aaaaaa	123
13	aaa aaaa aaa aaaaaaa aaa a aa	12
Sheet1

How can I?

Thanks!

 

[wideboydixon]

Book1
	A	B	C
1	aaa aaaa aaaaaaaaa 123	aaa aaaa aaaaaaaaa	123
2	aaaaaaa aaaa 12345	aaaaaaa aaaa	12345
3	aaa aa 1234567	aaa aa	1234567
4	aaaaaaaaaa aaaaaa 123	aaaaaaaaaa aaaaaa	123
5	aaa aaaa aaa aaaaaaa aaa a aa 12	aaa aaaa aaa aaaaaaa aaa a aa	12
Sheet1

Cell Formulas
Range	Formula
B1	=TRIM(LEFT(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1))
C1	=MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),LEN(A1))

WBD

 

[peter2047]

Here is a VBA macro that takes input from Sheet1 and output to Sheet2.
Assume only one numeric in each string.

Here is the code.

Sub split_alpha_num()

Dim alpha_num As Variant
Dim i As Integer, j As Integer
Dim alpha_string As String

i = 1

While ThisWorkbook.Sheets("Sheet1").Cells(i, 1) <> ""
alpha_num = Split(ThisWorkbook.Sheets("Sheet1").Cells(i, 1), " ")
alpha_string = ""
For j = 0 To UBound(alpha_num)
If Not IsNumeric(alpha_num(j)) Then
alpha_string = alpha_string & " " & alpha_num(j)
End If
Next j
ThisWorkbook.Sheets("Sheet2").Cells(i, 1) = alpha_string
For j = 0 To UBound(alpha_num)
If IsNumeric(alpha_num(j)) Then
ThisWorkbook.Sheets("Sheet2").Cells(i, 2) = alpha_num(j)
Exit For
End If
Next j
i = i + 1
Wend

End Sub

 

[SpillerBD]

Flash Fill can probably learn after two or three lines. (Excel 2013 and later)
https://www.youtube.com/watch?v=qHkgJFJR5cM

 

[smallxyz]

Hi WBD, your solution works well.
It is observed that there is no need for Ctrl + Shift + Enter for this formula.
Why is that so?
Thanks.

 

[smallxyz]

Thanks Peter and Spiller as well!

 

[Rick Rothstein]

Is the number that you want always after the last space in the text? If so, here is another solution you may want to consider (the formulas should be more efficient than what you have now)...

B1: =SUBSTITUTE(A1," "&C1,"")

C1: =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",200)),200))

 

[wideboydixon]

Hi WBD, your solution works well.
It is observed that there is no need for Ctrl + Shift + Enter for this formula.
Why is that so?
Thanks.

The SEARCH() function takes an array constant of {0,1,2,3,4,5,6,7,8,9} which causes the SEARCH() to be executed 10 times and then the MIN() function takes the lowest value.

WBD