Splitting cells to columns

[senman]

I currently have a column with data that I would like to
split into two columns. The column contains thousands of
records. Is there an easy way to do this? Here is a
sample of information in the current column.

5mg
10ug
100ml
100 test
0.5mg

I would like to split as follows:

5 mg
10 ug
100 ml
100 test
0.5 mg

thanks in advance for your help.

 

[Max]

One possible approach ..

Assuming the data is in col A, A2 down

Put in D1: =CHAR(COLUMN()+93)

Copy D1 across to AC1

[Just a quick way to fill D1:AC1 with the letters a ... z]

Put in D2:

=IF(ISERROR(SEARCH(D$1,$A2)),"",SEARCH(D$1,$A2))

Copy D2 across to AC2

Put in AD2: =MIN(IF(D2:AC2>0,D2:AC2))

Array-enter the formula by pressing
CTRL+SHIFT +ENTER
(instead of just pressing ENTER)

Excel will wrap curly braces { } around the formula, viz.:
{=MIN(IF(D2:AC2>0,D2:AC2))}

Put in B2: =LEFT(A2,AD2-1)*1
Put in C2: =TRIM(MID(A2,AD2,99))

Now just select B2:AD2 and copy down
as many rows as there is data in col A

Cols B and C will return
the splits required for the data in col A
(numbers in col B, text in col C)

 

[Juan Sanchez]

Senman...

You can use this approach which requires for a helper
column:

Say you have your data in column A, Starting on A1, in B1
type in (or copy):

B1: =MIN(IF((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)<>".")*
(ISERROR(VALUE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))))
=1,ROW(INDIRECT("1:"&LEN(A1))),""))

and input as array formula (CTRL+SHIFT+ENTER), you'll know
if you did it right if braces appear arround the formula:
{FORMULA}

That will give you the position of the first text char
(excluding period ".") on the string.
The use the following formulae for C1 and D1:

C1:=--LEFT(A1,B1-1).. This will return the value.
D1:=TRIM(MID(A1,B1,LEN(A1))).. this will return the unit.

The fill down for as much rows as you need...

Finally you can either keep the four columns or you can
copy C and paste.special > values and then delete A:B

Please post back with doubts if any...

Cheers
Juan

 

[Stephen Dunn]

Hi Senman,

This is a similar approach to Juan's, without the helper column:

=LEFT(A1,MAX(NOT(ISERR(-MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))*
ROW(INDIRECT("1:"&LEN(A1)))))

entered as an array formula (hold Ctrl+Shift when pressing Enter) in B1,
then:

=TRIM(RIGHT(A1,LEN(A1)-LEN(B1)))

(not an array formula) in C1.

copy B1:C1 downwards as far as required.

Steve D.

 

[senman]

This was awesum !! Worked great and will save alot of time.

thank you!!

 

[Max]

Pleasure' Senman !
Thanks for the feedback ..