Separate Numeric and Text data

Hi

I have the following data in a spreadsheet, for example
starting from A1:

1
2
3
14a
1ab
123c

I want to be able to separate this data into two
columns. Have tried Text To Columns, and have had no
luck.

There are no separators between the data, and the data is
of varying lengths - any ideas would be most appreciated.

B1:

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

C1:

=SUBSTITUTE(A1,B1,"")

The formulas expect input in A1 in the form DigitSequence followed by text,
with no digit in the latter part.

"MG" wrote in message
...
Hi

I have the following data in a spreadsheet, for example
starting from A1:

1
2
3
14a
1ab
123c

I want to be able to separate this data into two
columns. Have tried Text To Columns, and have had no
luck.

There are no separators between the data, and the data is
of varying lengths - any ideas would be most appreciated.

This from Jason Morin appears to work, I've no idea how -

=LEFT(A1,SUM(1*NOT(ISERROR(1*(MID(A1,ROW(INDIRECT( "1:"&LEN(A1))),1))))))&"
"&RIGHT(A1,LEN(A1)-SUM(1*NOT(ISERROR(1*(MID(A1,ROW(INDIRECT("1:"&LEN( A1))),1
))))))

This is an array formula, you'll need to use CTRL+Shift+Enter instead of
plain Enter.

From there, you should be able to copy & paste special (values), then
Data -- Text to Columns.

Rgds,
Andy

Another way

=--MID(A1,MATCH(FALSE,ISERROR(--MID(A1,ROW(INDIRECT("1:100")),1)),0),100-SUM
(--ISERROR(1*MID(A1,ROW(INDIRECT("1:100")),1))))

entered with ctrl + shift & enter

will work if the numbers always are together, if not post back

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

"MG" wrote in message
...
Hi

I have the following data in a spreadsheet, for example
starting from A1:

1
2
3
14a
1ab
123c

I want to be able to separate this data into two
columns. Have tried Text To Columns, and have had no
luck.

There are no separators between the data, and the data is
of varying lengths - any ideas would be most appreciated.

Hi,

B1:
=IF(ISTEXT(A1),--LEFT(A1,SUMPRODUCT(COUNTIF(A1,"*"&{".",0,1,2,3,4,5 ,6,7,8,9}
&"*"))),A1)
C1: =SUBSTITUTE(A1,B1,"")


--
Regards,
Soo Cheon Jheong
Seoul, Korea
_ _
^¢¯^
--

Excellent, this works well.
-----Original Message-----
Hi,

B1:
=3DIF(ISTEXT(A1),--LEFT(A1,SUMPRODUCT(COUNTIF(A1,"*"&
{".",0,1,2,3,4,5,6,7,8,9}
&"*"))),A1)
C1: =3DSUBSTITUTE(A1,B1,"")


--
Regards,
Soo Cheon Jheong
Seoul, Korea
_ _
^=A2=AF^
--=20


.

Thanks, works well.
-----Original Message-----
B1:

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

C1:

=SUBSTITUTE(A1,B1,"")

The formulas expect input in A1 in the form
DigitSequence followed by text,
with no digit in the latter part.

"MG" wrote in
message
...
Hi

I have the following data in a spreadsheet, for example
starting from A1:

1
2
3
14a
1ab
123c

I want to be able to separate this data into two
columns. Have tried Text To Columns, and have had no
luck.

There are no separators between the data, and the data
is
of varying lengths - any ideas would be most
appreciated.


.

Thanks, I'll try this.
-----Original Message-----
Another way

=--MID(A1,MATCH(FALSE,ISERROR(--MID(A1,ROW(INDIRECT
("1:100")),1)),0),100-SUM
(--ISERROR(1*MID(A1,ROW(INDIRECT("1:100")),1))))

entered with ctrl + shift & enter

will work if the numbers always are together, if not
post back

--


For everyone's benefit keep the discussion in the
newsgroup.

Regards,

Peo Sjoblom

"MG" wrote in
message
...
Hi

I have the following data in a spreadsheet, for example
starting from A1:

1
2
3
14a
1ab
123c

I want to be able to separate this data into two
columns. Have tried Text To Columns, and have had no
luck.

There are no separators between the data, and the data
is
of varying lengths - any ideas would be most
appreciated.


.

Thanks, I'll try this.
-----Original Message-----
This from Jason Morin appears to work, I've no idea how -

=LEFT(A1,SUM(1*NOT(ISERROR(1*(MID(A1,ROW(INDIRE CT
("1:"&LEN(A1))),1))))))&"
"&RIGHT(A1,LEN(A1)-SUM(1*NOT(ISERROR(1*(MID(A1,ROW
(INDIRECT("1:"&LEN(A1))),1
))))))

This is an array formula, you'll need to use
CTRL+Shift+Enter instead of
plain Enter.

From there, you should be able to copy & paste special
(values), then
Data -- Text to Columns.

Rgds,
Andy


.

Not with duplicate digits in the didit sequence...

Try:

1222Saturnus

"mg" wrote in message
...
Excellent, this works well.
-----Original Message-----
Hi,

B1:
=IF(ISTEXT(A1),--LEFT(A1,SUMPRODUCT(COUNTIF(A1,"*"&
{".",0,1,2,3,4,5,6,7,8,9}
&"*"))),A1)
C1: =SUBSTITUTE(A1,B1,"")


--
Regards,
Soo Cheon Jheong
Seoul, Korea
_ _
^¢¯^
--


.