How to split 1 column in excel into 2 separate columns

Question

Question

Given a single column of weight_and_units which looks something like:

weight_and_units
10Ounces
10Ounces
10Ounces
10Ounces
10Ounces
10Pounds
10Pounds
10Pounds
10Pounds
10Pounds
11Ounces
11Ounces
11Ounces
11Ounces
11Ounces
11Pounds
11Pounds
11Pounds
11Pounds
11Pounds
12Ounces
12Ounces
12Ounces
12Ounces
12Ounces

I want to be able to create 2 separate columns like

weight
10
11
12
13

and

units
Ounces
Pounds
Pounds

Tried

I have looked into the process outlined in this link: Creating 2 columns from 1 however this seems to only work for a column which has a set delimiter etc and as a result I don't think it would work in this scenario.

Any method (ideally the simplest) to do this would be much appreciated.

Answer 1

I liked the flash-fill option but if you go with formulas you could try the following:

Formula in B2:

=-LOOKUP(1,-LEFT(A2,ROW($1:$99)))

Formula in C2:

=MID(A2,LEN(B2)+1,99)

Answer 2

I am gonna tell you the simplest method using the flash fill without any formulas. This will work for any number of digits

Just fill the data manually in for the first line like the below,

Weight and Unit	Weight	Ounces
10Ounces	10	Ounces
10Pounds
10Pounds
10Pounds
11Pounds

Step1: Select the cell Weight and Click Ctrl + E to fill the Weight Column.

Step2: Select the cell Unit and Click Ctrl + E to fill the Unit Column.

Output:

Weight and Unit	Weight	Ounces
10Ounces	10	Ounces
10Pounds	10	Pounds
10Pounds	10	Pounds
10Pounds	10	Pounds
11Pounds	11	Pounds

More info on Flash fill:

Data -> Data Tools -> Flash Fill

Answer 3

In column B:

=LEFT(A2;MATCH(TRUE;ISERROR(VALUE(MID(A2;ROW(INDIRECT("1:"&LEN(A2)));1)));0)-1)

In column C:

=MID(A2;FIND(B2;A2;1)+2;50)

Answer 4

For Number

LEFT(A1,SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{"0","1","2","3","4","5","6","7","8","9"},""))))

For Text

RIGHT(A1, Len(the above value)